I’m slowly working through the Advent of Code 2022 puzzles using PostgreSQL and SQL as much as possible. For an overview of how I’m approaching these, please see the first post in this series: Advent of Code 2022: Days 1-5 with PostgreSQL
Other resources:
AoC 2022: Day 6-10 with PostgreSQL
Day 6: Tuning Trouble
A fun puzzle, we were given a long string of characters which we then had to find sets of transmission markers by identifying non-repeating characters.
For Star 1, we had to find the “start-of-packet” marker by finding the first set of four non-repeating characters. After some thought, I chose to split the long string into smaller sets of four character arrays and then doing a DISTINCT to identify ones that had four distinct characters.
Star 2 was similar, only we had to identify the “start-of-message” marker which was 14 consecutive, non-repeating characters. I simply changed a few values to create arrays of 14 characters to solve this puzzle.
Day 7: No Space Left On Device
Directory structures and recursive CTEs. Whenever someone teaches about recursive CTEs they either use a manager/employee example or a directory structure, typical parent/child relationships. So while I felt like this should have been easy, for some reason I struggled to get the right relationship. Once I did (with some help), things fell into place.
Star 1 entailed creating the correct parent/child relationship so that we could sum the total file space of each directory in the tree. This meant that a parent fold was the sum of the files it contained and the sum of all child folders. Great usage of regexp_match and CARDINALITY to help solve this puzzle.
I kept getting close, but honestly couldn’t have solved it in a timely manor without seeing what someone else did to get the correct parent/child sums. Great learning experience.
Star 2 took the input from the first star to then figure out the smallest folder that could be deleted to free up enough space to do an update. I’m sure there were more elegant solutions, but I simply did some additional math and sub-selects to get the final answer.
Day 8: Treetop Tree House
Early in my career I was a teacher for 12-18 year old (middle and high school for U.S. folks). Part of my teach load was technology classes, both programming and basics like word processing and spreadsheets. I learned early on that most kids rarely read past the first line of a test question or the first choice in a spellcheck list (I got many papers addressed to Mr. Boo).
Day 8 was my own little reminder to read the full instructions. The puzzle instructions and examples were clear… I just didn’t read them carefully. This meant that I approached Star 1 wrong from the beginning and wasted some time. Once I realized I was wrong, my second try helped.
For Star 1 we had to look at a grid of numbers representing the height of trees to find the total number of visible trees. A tree was visible if it could be seen from any direction (row/column) all the way to the edge. The final solution used string_to_table (new in PostgreSQL 14+) and some basic WHERE predicates. I also saw a better solution from Vik Fearing using WINDOW functions which was so much simpler. 🤦♂️
Star 2 was a bit closer to what I was originally trying for the first star, but this time we had to find a “scenic score” for each tree using some basic math and a final ORDER BY.
Quick note: I realized after recording the video that one of my “clean up” edits on the SQL actually produced the wrong answer in the video because it relied on the identify column of my data starting at 1. I’ve updated the GitHub repo code to ensure it works regardless of the ID, but didn’t feel it was worth re-recording the video. Live and learn…
Day 9: Rope Bridge
I’m not going to lie on this one. Day 9 really got me. The basics of how to approach this puzzle seemed clear, but I once again missed an important detail. We had to track the movement of movements for the head of a rope and then adjust the tail of the rope to follow along based on the rules presented. “Simple enough,” I thought, “perfect use of a recursive CTE”. That did work in the end, but not without some trials. The solution for each puzzle was about tracking how many unique coordinates the tail visited during all of the movement.
Star 1 was only about two “knots”, a head and a tail. We were provided the movement of the head and then as the tail . The big thing I missed the first time around is that we had take one step at a time and adjust the tail accordingly. Instead, my first attempt simply made the total movement each time, which resulted in significantly under-counting the movement of the tail. Lesson learned.
Star 2 was just the first star… on steroids!! Now we had to track the movement of 10 knots while still only knowing where the first knot (the head) moved. I kept getting close, but because the movement of each knot was predicated on the movement of the knot before it, I kept missing one movement value. The simple formula that Vik used in his solution was the final key for me.
My final solution here used a multi-dimensional array with a recursive CTE to track the movement of each knot as things went. By cutting out additional work, I was able to cut my initial solution down from ~25 minutes to ~1.5 minutes.
Day 10: Cathode-Ray Tube
Honestly the second star has been my favorite answer so far. Getting there seemed simple enough, but off-by-one issues just kept annoying me! Once again I entered the puzzle with what seemed like a really easy solution, only to get complicated 10 minutes later. After solving the puzzle I saw others solved this much more easily with something like string_to_table and a WINDOW function. One of these days I’ll learn!
Star 1 tracked the value of an integer in the register as each CPU cycle clicked by given a set of instructions. Again, I got the answer, but not without overcomplicating it.
Star 2 was honestly just really fun. We had to use the values in the register from Star 1, using them to identify pixels on a small CRT that needed to be turned on. Once that was completed, we had to “draw” the screen to see a set of capital letters. Something about taking a table of data and calling string_agg to reveal the letters just felt fun.
Key Takeaways
I have no idea how long it will take me to try and work through all 25 puzzles. In ten “days”, I’m amazed at how much I’ve learned, both about how I approach this kind of problem and how there are always multiple ways to the same ends. Learning the strengths and weaknesses of your tool, PostgreSQL in this case, is essential to improving your knowledge for the next task.
And again, I’m thankful to others like Vik and Feike that are sharing their work so that I can learn more, too!