際際滷

際際滷Share a Scribd company logo

ClickHouse SQL Games

R
rpolat

Ramazan Polat presented ClickHouse SQL games at a ClickHouse Istanbul Meetup. He demonstrated two games - a number guessing game and a WORDLE clone - that were implemented using ClickHouse SQL queries. Polat showed how WORDLE could be "cheated" by filtering the word list based on known letter placements. He invited others to contribute additional ClickHouse SQL games to a GitHub repository.

1 of 10
Download to read offline
ClickHouse SQL Games by Ramazan Polat ClickHouse Istanbul Meetup - 2022-10-21
Intro Ramazan Polat ClickHouse Developer at Santiment.net is a behavior analytics platform for cryptocurrencies, sourcing on-chain, social and development information on 2000+ coins. Title: ClickHouse SQL Games Author: Ramazan Polat Event: ClickHouse Istanbul Meetup Date: 2022-10-21 Organizers: Altinity and P.I. Works Repo: https://github.com/ramazanpolat/clickhouse-sql-games 際際滷s: TBA
What ClickHouse can do Run on any architect (x86, ARM, M1, RISC, Power9, ) Scale to 1000s of nodes Run AI models Beat almost any competitor in benchmarks (the list goes on and on) How about playing games?
What games? Not like this one * Image generated with Midjourney
You imagine a number in your head and let ClickHouse guess the number DEMO 1st game: Guess the number Game code: https://github.com/ramazanpolat/clickhouse-sql-games
2nd game: WORDLE Game code: https://github.com/ramazanpolat/clickhouse-sql-games DEMO
Bonus: WORDLE cheater 1. Goto https://bit.ly/sql-games * 2. Put gray letters in BLACKLIST, yellow and green ones in WHITELIST then run query WITH 'abc' AS BLACKLIST, 'de' AS WHITELIST SELECT num, upper(word) FROM ( WITH splitByString('',word) AS word_arr, splitByString('', lower(BLACKLIST)) AS black_arr, splitByString('', lower(WHITELIST)) AS white_arr, arraySum(arrayMap( black -> position(black IN word),black_arr))>0 as has_blacklist_letters, arrayMap(white -> position(white IN word)>0, white_arr) as white_arr_comp, arraySum(white_arr_comp) == length(white_arr) as has_whitelist_letters SELECT rowNumberInAllBlocks()+1 AS num, word FROM url('https://raw.githubusercontent.com/ramazanpolat/clickhouse-sql-games/main/wordle/sgb-words.txt','CSV','word String') WHERE has_blacklist_letters=0 AND has_whitelist_letters=1 ) ORDER BY word; * Full url: https://fiddle.clickhouse.com/4d557fe5-479d-4de2-b0c7-6baa64bcd8d4
a) Chat bot b) Order pizza c) Play Doom! d) Other: ___________________ What next?
If you are a ClickHouse SQL game developer, you can send your game to the following repo with a PR: https://github.com/ramazanpolat/clickhouse-sql-games Your move!
Thank You! You can find games and codes in the following repo https:/ /github.com/ramazanpolat/clickhouse-sql-games ramazanpolat@gmail.com https:/ /www.linkedin.com/in/ramazanpolat/

More Related Content

ClickHouse SQL Games

  • 1. ClickHouse SQL Games by Ramazan Polat ClickHouse Istanbul Meetup - 2022-10-21
  • 2. Intro Ramazan Polat ClickHouse Developer at Santiment.net is a behavior analytics platform for cryptocurrencies, sourcing on-chain, social and development information on 2000+ coins. Title: ClickHouse SQL Games Author: Ramazan Polat Event: ClickHouse Istanbul Meetup Date: 2022-10-21 Organizers: Altinity and P.I. Works Repo: https://github.com/ramazanpolat/clickhouse-sql-games 際際滷s: TBA
  • 3. What ClickHouse can do Run on any architect (x86, ARM, M1, RISC, Power9, ) Scale to 1000s of nodes Run AI models Beat almost any competitor in benchmarks (the list goes on and on) How about playing games?
  • 4. What games? Not like this one * Image generated with Midjourney
  • 5. You imagine a number in your head and let ClickHouse guess the number DEMO 1st game: Guess the number Game code: https://github.com/ramazanpolat/clickhouse-sql-games
  • 6. 2nd game: WORDLE Game code: https://github.com/ramazanpolat/clickhouse-sql-games DEMO
  • 7. Bonus: WORDLE cheater 1. Goto https://bit.ly/sql-games * 2. Put gray letters in BLACKLIST, yellow and green ones in WHITELIST then run query WITH 'abc' AS BLACKLIST, 'de' AS WHITELIST SELECT num, upper(word) FROM ( WITH splitByString('',word) AS word_arr, splitByString('', lower(BLACKLIST)) AS black_arr, splitByString('', lower(WHITELIST)) AS white_arr, arraySum(arrayMap( black -> position(black IN word),black_arr))>0 as has_blacklist_letters, arrayMap(white -> position(white IN word)>0, white_arr) as white_arr_comp, arraySum(white_arr_comp) == length(white_arr) as has_whitelist_letters SELECT rowNumberInAllBlocks()+1 AS num, word FROM url('https://raw.githubusercontent.com/ramazanpolat/clickhouse-sql-games/main/wordle/sgb-words.txt','CSV','word String') WHERE has_blacklist_letters=0 AND has_whitelist_letters=1 ) ORDER BY word; * Full url: https://fiddle.clickhouse.com/4d557fe5-479d-4de2-b0c7-6baa64bcd8d4
  • 8. a) Chat bot b) Order pizza c) Play Doom! d) Other: ___________________ What next?
  • 9. If you are a ClickHouse SQL game developer, you can send your game to the following repo with a PR: https://github.com/ramazanpolat/clickhouse-sql-games Your move!
  • 10. Thank You! You can find games and codes in the following repo https:/ /github.com/ramazanpolat/clickhouse-sql-games ramazanpolat@gmail.com https:/ /www.linkedin.com/in/ramazanpolat/