Advent of Code SQL

Sort each puzzle by what its second half asks. If it asks you to total or join rows you already have, SQL is shorter than a script. Reading ranges, matching pairs of lines, counting overlaps and scoring hands are set operations, so a GROUP BY or a window function does the work of a loop.

If it asks you to apply a rule again and again until nothing changes, you need iteration: a grid that evolves, a graph that is walked, a counter that grows each round. SQL does this with a recursive query, and the number of rounds is decided by the data, which is exactly the case recursion exists for.

A third group is closed to brute force in any language. One public write-up of the 2023 event in SQL found a day whose answer needed roughly 14.9 trillion steps, so simulating it was out and the way through was to notice a property of the input. Expect to read the input for structure before you write a line, in SQL as in anything else.

Budget matters too. The event states that every puzzle has a solution that runs in at most 15 seconds on 10-year-old hardware. A query that has not finished after a minute usually means a join that multiplies rows, or a recursive step that keeps rows it should drop.

Every puzzle gives you a text file, and the first job is turning it into rows. Load each line into a table with a line number, raw(line_no, line), using your database's bulk load command or a short script. Keep the loader separate from the query so the query reads as the solution.

Parse in a first CTE and name the columns the puzzle talks about. Split on the delimiter and cast to integers. Keep the line number so you can reorder or join on position later. Most wrong answers come from this step, a stray blank line or a negative number cast the wrong way, and not from the clever part.

Run the puzzle's own small example first. The statement gives an expected answer for it, which is a test you did not have to write. When the example passes and the real input does not, the cause is almost always a shape the example never showed: a longer line, a duplicate key, an empty group.

Write part 1 and part 2 as 2 queries over the same parsed CTE. Part 2 usually changes one rule, and a readable part 1 makes the change a small edit.

Some puzzles are possible in SQL and not pleasant. A grid that updates in place, a program with registers, or a rule that depends on the order in which things happen all want mutable state, and a recursive query has to store state as a row per step. The query gets long, and the work per step grows with the number of rows you keep. Pick the days that suit sets and graphs, and solve the rest in a script without guilt.

The event's about page sets a few rules that affect a SQL repository. The global leaderboard was removed after 10 years, with private leaderboards remaining, so a friends' board is the way to compare. The FAQ answers no to using AI to solve the puzzles. And you may link to puzzles but should not post puzzle text or your inputs in repositories, so keep the inputs in a git-ignored folder and publish the queries.

For the loop days specifically, read recursive CTEs for hierarchies and graphs before the event and how a CTE differs from a subquery if the naming style is new to you.

A calendar rewards a steady cadence. Advent of SQL's 2024 edition sent 1 challenge a day for 24 days, with difficulty rising over the month and recursive SQL arriving around day 9. Skipping a few days costs little, since past challenges stay available after you sign in, so catching up in 1 sitting is a fair way to keep the habit.

The value is in the second pass. After a challenge works, rewrite it so a colleague could review it: name the CTEs for the business idea, remove the columns you carried by accident, and check that it still returns the same rows on a different input.

Compare notes with other people. The Advent of SQL community gathers on a subreddit, and the DataDriven.io discussion forum is a place to ask how someone else approached a query you found awkward.

December puzzles are short and self-contained. Data engineering work is long and messy, and a team shares it, so use the event for the query muscle and then point it at problems with an engineering shape. The DataDriven.io Weekly is one community data engineering challenge a week, scored blind on hidden data, with a brief, a small sample dataset and a starter file.

When the loop days feel solid, move to the questions an interviewer asks. SQL interview questions for data engineers cover the joins and aggregation that a recursive puzzle never touches, and SQL practice problems give you queries to write against data you did not design. The SQL round guide explains what the round looks for, and window function interview questions go deeper on the one feature most puzzle solutions skip.