Advent of Code is a yearly coding challenge created by Eric Watsl (@ericwastl) that runs from December 1-25. I’ve seen it referenced over the last 6-7 years in various technical circles, but never registered and tried to follow along.
This year, I started to see Vik Fearing and Feike Steenbergen tweet about it, specifically solving the puzzles using PostgreSQL. The specific challenge they presented for themselves was to only use SQL and base functions provided by PostgreSQL, hoping to avoid functions or stored procedures that require a language like pl/pgsql.
The idea intrigued me and I decided to work alongside my fellow PostgreSQL community on AoC, learning all I could in the process. (spoiler alert… I’ve learned a lot already!).
As I go, I’ll upload my finished code to my repo and (hopefully) record a video for each day explaining my approach. I’ve finished through day 8 (as of December 14, 2022), but haven’t finished all of the videos yet. Again, my goal is to enjoy the challenge and learn, not try to be the first one done or get on a leaderboard somewhere. This also means it will probably take into January 2023 to finish the puzzles and record videos.
Enough explanation! Let’s talk about the first five puzzles, including links to the videos.
AoC 2022: Day 1-5 with PostgreSQL
Day 1: Calorie Counting
This was an interesting first start for SQL. I originally solved this using a complicated back and forth between a rows and arrays. The puzzle provided input that represented the calorie values for various food items that each elf had with them. The group of items for each elf differed, and the only way to determine the separation of groups was with an empty row in the text file.
The simpler solution was using a FILTER statement on an aggregation of the groups. (Thanks to Feike for that hint).
Day 2: Rock, Paper, Scissors
So far this has felt like the “easiest” puzzle for me. Although I probably could have completed it with less typing, the concept was pretty straight forward and accomplished with joining two related tables and adding the appropriate point amount.
Day 3: Rucksack Reorganization
There’s something about simple tasks like splitting strings and creating tables to solve a puzzle. Not the least of which is remembering that most of these tasks in PostgreSQL are 1-based when splitting a string in half doesn’t quite work out the first time. 😀 In the end, it was simplest to create couple of tables with a CTE and join to find the “uniqueness required to “shared items” and solve the puzzle.
Day 4: Camp Cleanup
This puzzle was all about overlapping ranges of numbers. I had recently been reviewing the comparison operators for both arrays and range types which made this puzzle pretty easy to solve once the data was prepared. For the fun of it, I did it with both arrays and range types.
Day 5: Supply Stacks
This puzzle entailed iterating through instructions to move items between stacks. I knew this would probably entail using a recursive CTE without the ability to use a secondary language with loops and conditionals. And, to be honest, recursive CTE’s always make my brain work harder than they should, but after some work I was able to make it happen.
In the end, I chose to only use an array of strings to move items around the stacks which honestly made it a bit harder to get to my solution. After the fact, I saw that others in the community quickly created a JSONB object from the data so that they could use the jsonb_set function which was a bit more clean.
Because I had to put some mental effort into creating the recursive CTE correctly, I spent ~10 minutes in my video recap for this day talking about how recursive CTE’s work and how it helped me solve the day 5 puzzle.
Key Takeaways
I’m happy that I saw others in the PostgreSQL community mention Advent of Code. The opportunity to think about data in a new way and think about how to process and query it without variables and traditional procedural conveniences has been fun (if not difficult a few times) and displays how powerful PostgreSQL and SQL are.
I can’t wait to see what the next 20 challenges have in store… and what I’ll inevitably learn through the process.
1 thought on “Advent of Code 2022: Days 1-5 With PostgreSQL”