Greg Sabino Mullane: How to Solve Advent of Code 2022 Using Postgres - Day 15

This article will contain spoilers both on how I solved 2022 Day 15's challenge "Beacon Exclusion Zone" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up. Hands on Tutorial We've also loaded a tutorial for Day 15's challenge if you want to try it with a pre-loaded data set. AOC Day 15 For this day's challenge, we are presented with an input file that contains the locations of many sensors, along with the closest beacon to that sensor. The first goal is to find out how many beacons CANNOT be in a certain row. The distance from the sensor to the beacon is measured in taxi distance also known as the snake distance or the Manhattan distance. See the Wikipedia entry if you are unfamiliar with it, as it will help to understand this problem. The file consists of lines like these: Sensor at x=1555825, y=18926: closest beacon is at x=1498426, y=-85030 Sensor at x=697941, y=3552290: closest beacon is at x=595451, y=3788543 Sensor at x=3997971, y=2461001: closest beacon is at x=3951198, y=2418718 As always, we are going to employ a handful of Postgres / SQL tools to help us solve this, including: file_fdw to read text files in from the system sequences to help us do some basic counting The regexp_substr and regexp_match functions to help extract numbers from a text string CTEs Common Table Expressions to keep our queries readable and not descending into sub-select chaos. The int4range range type, to efficiently gather together a lot of numbers along a line The range_agg and unnest functions to help us sort the int4range output The ctid system column as a quick way to exclude the "current" row from a[...]