4 ms·

Author here. I had the idea of building a working Chess game using purely SQL. The chess framing is a bit of a trojan horse, honestly. The actual point is tha

Author here. I had the idea of building a working Chess game using purely SQL. The chess framing is a bit of a trojan horse, honestly. The actual point is that SQL can represent any stateful 2D grid. Calendars, heatmaps, seating plans, game of life. The schema is always the same: two coordinate columns and a value. The pivot query doesn't change. A few people have asked why not just use a 64-char string or an array type. You could! But you lose all the relational goodness: joins, aggregations, filtering by piece type. SELECT COUNT(*) FROM board WHERE piece = '♙' just works.

eastbound 6mo ago
SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. “Pivot tables”: I often have a list of dates, then categories that I want to become columns. SQL can’t do that so there is a technique of spreading values to each column then doing a MAX of each value per date. It is clumsy and verbose but works perfectly… as long as categories are known in advance and fixed. There should be an SQL instruction to pivot those rows into columns. Example: SELECT date, category, metric; -- I want to show 1 row per date only, with each category as a column. ``` SELECT date, MAX( CASE category WHEN ‘page_hits’ THEN metric END ) as “Page Hits”, MAX( CASE category WHEN ‘user_count’ THEN metric END ) as “User Count” GROUP BY date; ^ Without MAX and GROUP BY: 2026-03-30 Value1 NULL 2026-03-30 NULL Value2 2026-03-31 Value1 NULL (etc) The MAX just merges all rows of the same date. ``` SQL should just have an instruction like: SELECT date, PIVOT(category, metric); to display as many columns as categories. This thought should be extended for more than 2 dimensions.
- tn1 6mo ago
  DuckDB and Microsoft Access (!) have a PIVOT keyword (possibly others too). The latter is of course limited but the former is pretty robust - I've been able to use it for all I've needed.
  - amichal 6mo ago
    PostgresSQL "crosstab ( source_sql text, category_sql text ) → setof record" https://www.postgresql.org/docs/current/tablefunc.html https://www.postgresql.org/docs/current/tablefunc.html VIA https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgresql/ https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgres... as a current googlable reference/guide
- andersmurphy 6mo ago
  > SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. R*Trees are what you are looking for. The sqlite implementation supports up to 5 dimensions.
- h3lp 6mo ago
  in sqlite you can do it with FILTER: $ sqlite :memory: create table t (product,revenue, year); insert into t values ('a',10,2020),('b',14,2020),('c',24,2020),('a',20,2021),('b',24,2021),('c',34,2021); select product,sum(revenue) filter (where year=2020) as '2020',sum(revenue) filter (where year=2021) as '2021' from t group by product;
andoando 6mo ago
Technically you can model anything in SQL including execution of any Turing complete language
- eru 6mo ago
  Yes, but OP wants to preserve the relational goodness.
jollygoodshow 6mo ago
Great showcase. Cool to see how any 2d state can be presented with enough work. Just FYI your statement for the checkmate state in the opera game appears to be incorrect
- upmostly 6mo ago
  Thank you, and thanks for highlighting that. I'll take a look now.
mwigdahl 6mo ago
Can you comment on whether you wrote the article yourself or used an LLM for it? To me it reads human (in a maybe slightly overly-punchy, LinkedIn-esque way), but a lot of folks are keying on the choppiness and exclusion chains and concluding it's AI-written. I'm interested in whether others are oversensitive or I'm not sensitive enough... :)
- slopinthebag 6mo ago
  They definitely used an LLM for it
andersmurphy 6mo ago
Yup. Works even better with R*Trees[1]. Great article btw! - [1] https://www.sqlite.org/rtree.html https://www.sqlite.org/rtree.html
traderj0e 6mo ago
I've done huge sparse matrix math with SQL before. It parallelizes well like you'd expect.
btown 6mo ago
One of the things that LLMs "excel" at, pun very much intended, is this exact pattern - creating filtered aggregates for a finite set of columns, and using this at the end of a CTE! OP's example, for reference, was: SELECT rank, MAX(CASE WHEN file = 1 THEN COALESCE(piece, '·') END) AS a, MAX(CASE WHEN file = 2 THEN COALESCE(piece, '·') END) AS b, MAX(CASE WHEN file = 3 THEN COALESCE(piece, '·') END) AS c, MAX(CASE WHEN file = 4 THEN COALESCE(piece, '·') END) AS d, This pattern is incredible for generating financial model drivers (where every column is a calendar/fiscal month/quarter/year, and every row is a different type of statistic/measure). The broader pattern is, in successive CTEs: 1. Group by Date w/ Aggregates 2. "Melt" to [optional groupings +] month + measure_name + value tuples: select group, month, '# Bookings' as measure_name, num_bookings as value from base_data UNION ALL select group, month, 'Revenue', total_revenue from base_data 3. Then "pivot": MAX(CASE WHEN month = '2019-01' THEN value END) AS "2019-01", MAX(CASE WHEN month = '2019-02' THEN value END) AS "2019-02", MAX(CASE WHEN month = '2019-03' THEN value END) AS "2019-03", And what you get is a full analysis table, with arbitrary groupings, that can be dropped into an Excel model in a way that makes life easy for business teams. And while the column breakouts are painful to type out by hand - they're very amenable to LLM generation!

eastbound 6mo ago

SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. “Pivot tables”: I often have a list of dates, then categories that I want to become columns. SQL can’t do that so there is a technique of spreading values to each column then doing a MAX of each value per date. It is clumsy and verbose but works perfectly… as long as categories are known in advance and fixed. There should be an SQL instruction to pivot those rows into columns. Example: SELECT date, category, metric; -- I want to show 1 row per date only, with each category as a column. ``` SELECT date, MAX( CASE category WHEN ‘page_hits’ THEN metric END ) as “Page Hits”, MAX( CASE category WHEN ‘user_count’ THEN metric END ) as “User Count” GROUP BY date; ^ Without MAX and GROUP BY: 2026-03-30 Value1 NULL 2026-03-30 NULL Value2 2026-03-31 Value1 NULL (etc) The MAX just merges all rows of the same date. ``` SQL should just have an instruction like: SELECT date, PIVOT(category, metric); to display as many columns as categories. This thought should be extended for more than 2 dimensions.

tn1 6mo ago
DuckDB and Microsoft Access (!) have a PIVOT keyword (possibly others too). The latter is of course limited but the former is pretty robust - I've been able to use it for all I've needed.
- amichal 6mo ago
  PostgresSQL "crosstab ( source_sql text, category_sql text ) → setof record" https://www.postgresql.org/docs/current/tablefunc.html https://www.postgresql.org/docs/current/tablefunc.html VIA https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgresql/ https://www.beekeeperstudio.io/blog/how-to-pivot-in-postgres... as a current googlable reference/guide
andersmurphy 6mo ago
> SQL can make 2D data, but it extremely bad at it. It’s a good opportunity to wonder whether this part can be improved. R*Trees are what you are looking for. The sqlite implementation supports up to 5 dimensions.
h3lp 6mo ago
in sqlite you can do it with FILTER: $ sqlite :memory: create table t (product,revenue, year); insert into t values ('a',10,2020),('b',14,2020),('c',24,2020),('a',20,2021),('b',24,2021),('c',34,2021); select product,sum(revenue) filter (where year=2020) as '2020',sum(revenue) filter (where year=2021) as '2021' from t group by product;

andoando 6mo ago

Technically you can model anything in SQL including execution of any Turing complete language

eru 6mo ago
Yes, but OP wants to preserve the relational goodness.

jollygoodshow 6mo ago

Great showcase. Cool to see how any 2d state can be presented with enough work. Just FYI your statement for the checkmate state in the opera game appears to be incorrect

upmostly 6mo ago
Thank you, and thanks for highlighting that. I'll take a look now.

mwigdahl 6mo ago

Can you comment on whether you wrote the article yourself or used an LLM for it? To me it reads human (in a maybe slightly overly-punchy, LinkedIn-esque way), but a lot of folks are keying on the choppiness and exclusion chains and concluding it's AI-written. I'm interested in whether others are oversensitive or I'm not sensitive enough... :)

slopinthebag 6mo ago
They definitely used an LLM for it

andersmurphy 6mo ago

Yup. Works even better with R*Trees[1]. Great article btw! - [1] https://www.sqlite.org/rtree.html https://www.sqlite.org/rtree.html

traderj0e 6mo ago

I've done huge sparse matrix math with SQL before. It parallelizes well like you'd expect.

btown 6mo ago

One of the things that LLMs "excel" at, pun very much intended, is this exact pattern - creating filtered aggregates for a finite set of columns, and using this at the end of a CTE! OP's example, for reference, was: SELECT rank, MAX(CASE WHEN file = 1 THEN COALESCE(piece, '·') END) AS a, MAX(CASE WHEN file = 2 THEN COALESCE(piece, '·') END) AS b, MAX(CASE WHEN file = 3 THEN COALESCE(piece, '·') END) AS c, MAX(CASE WHEN file = 4 THEN COALESCE(piece, '·') END) AS d, This pattern is incredible for generating financial model drivers (where every column is a calendar/fiscal month/quarter/year, and every row is a different type of statistic/measure). The broader pattern is, in successive CTEs: 1. Group by Date w/ Aggregates 2. "Melt" to [optional groupings +] month + measure_name + value tuples: select group, month, '# Bookings' as measure_name, num_bookings as value from base_data UNION ALL select group, month, 'Revenue', total_revenue from base_data 3. Then "pivot": MAX(CASE WHEN month = '2019-01' THEN value END) AS "2019-01", MAX(CASE WHEN month = '2019-02' THEN value END) AS "2019-02", MAX(CASE WHEN month = '2019-03' THEN value END) AS "2019-03", And what you get is a full analysis table, with arbitrary groupings, that can be dropped into an Excel model in a way that makes life easy for business teams. And while the column breakouts are painful to type out by hand - they're very amenable to LLM generation!