# Advent of Code 2024 in pure SQL

DevFeed: [Advent of Code 2024 in pure SQL](<https://devfeed.tech/articles/advent-of-code-2024-in-pure-sql-25086.md>)

Original publisher: [Read original article](<https://databasearchitects.blogspot.com/2024/12/advent-of-code-2024-in-pure-sql.html>)

Author: Thomas Neumann (noreply@blogger.com)

Published: 2024-12-27T16:57:00Z

Content type: article

Language: en

Sources: [Database Architects](<https://devfeed.tech/sources/database-architects.md>)

Topics: [SQL](<https://devfeed.tech/topics/sql.md>), [Advent of Code](<https://devfeed.tech/topics/advent-of-code.md>), [Algorithms](<https://devfeed.tech/topics/algorithms.md>), [Programming](<https://devfeed.tech/topics/programming.md>), [Graphs](<https://devfeed.tech/topics/graphs.md>)

Tags: [advent-of-code](<https://devfeed.tech/tags/advent-of-code.md>), [algorithm](<https://devfeed.tech/tags/algorithm.md>), [duckdb](<https://devfeed.tech/tags/duckdb.md>), [parsing](<https://devfeed.tech/tags/parsing.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [puzzle](<https://devfeed.tech/tags/puzzle.md>), [recursive-sql](<https://devfeed.tech/tags/recursive-sql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

## AI overview

The author describes solving every Advent of Code 2024 problem in pure SQL. Small-scale traversals were practical and sometimes pleasant, while larger recursive queries could be inefficient and require more than 200 GB of memory. The experience suggests that recursive SQL would benefit from mechanisms for updating state and supporting more complex control flow.

## Source excerpt

On a whim I decided to do this years advent of code in pure SQL. That was an interesting experience that I can recommend to everybody because it forces you to think differently about the problems. And I can report that it was possible to solve every problem in pure SQL. In many cases SQL was actually surprisingly pleasant to use. The full solution for day 11 (including the puzzle input) is shown below: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30with recursive aoc10_input(i) as (select ' 89010123 78121874 87430965 96549874 45678903 32019012 01329801 10456732 '), lines(y,line) as ( select 0, substr(i,1,position(E'\n' in i)-1), substr(i,position(E'\n' in i)+1) from aoc10_input union all select y+1,substr(r,1,position(E'\n' in r)-1), substr(r,position(E'\n' in r)+1) from lines l(y,l,r) where position(E'\n' in r)>0 ), field(x,y,v) as ( select x,y,ascii(substr(line,x::integer,1))-48 from (select * from lines l where line<>'') s, lateral generate_series(1,length(line)) g(x) ), paths(x,y,v,sx,sy) as ( select x,y,9,x,y from field where v = 9 union all select f.x,f.y,f.v,p.sx,p.sy from field f, paths p where f.v=p.v-1 and ((f.x=p.x and abs(f.y-p.y)=1) or (f.y=p.y and abs(f.x-p.x)=1)) and p.v>0), results as (select * from paths where v=0), part1 as (select distinct * from results) select (select count(*) from part1) as part1, (select count(*) from results) as part2 Parsing the input is a bit painful in SQL, but it is not too bad. Lines 1-10 are simply the puzzle input, lines 11-17 split the input into individual lines, and lines 18-21 construct a 2D array from the input. The algorithm itself is pretty short, lines 22-27 perform a recursive traversal of the field, and lines 28-39 extract the puzzle answer from the traversal results. For this kind of small scale traversals SQL works just fine. Other days were more painful. Day 16 for example does conceptually a very similar traversal of a field, and it computes the minimal traversal distance