Advent of SQL
Daily database puzzles solved in PostgreSQL.
About the project
Advent of SQL is a December challenge in the style of Advent of Code, but every puzzle is a database: each day brings a PostgreSQL dump about Santa's workshop and a question that has to be answered with a query.
I solved the first eleven days of the 2024 edition in plain SQL, one file per day with the answer noted under the query. The puzzles go beyond joins and grouping and call for window functions, recursive queries and working with JSON and XML.
Under the hood
- Window functions such as LAG and ROW_NUMBER for comparing rows with their neighbours and ranking within groups
- A moving average over the last three seasons with a ROWS BETWEEN window frame
- A recursive CTE to walk a management hierarchy and find how deep it goes
- XML menus stored in three different schema versions, told apart with xpath_exists and read with XPath
- Reading values from JSONB columns, and comparing arrays with EXCEPT and INTERSECT to find added, kept and removed tags
- Decoding a hidden message by filtering character codes and joining them with string_agg
- When
- 2024
- Type
- Personal
- Stack
- PostgreSQL