advent-of-code

Ok, this year I decided to try the Advent of Code. But to level up my SQL game, I decided to do it purely in PostgreSQL. No stored procedures, no tables, just pure logic over SQL (or, at least, PostgreSQL-flavored SQL). I’ll also try to not use PG specific datatypes like JSONB or Arrays/Ranges, but I can’t promise this – the challenge is already hard with the way it is.

So, I decided that the input will be a single table called input with a single record and field, called input. This means that I have to split the input in some way – I’m currently using regexp_split_to_table(input.input, '\n') to generate a table with all the lines when this makes sense.

So, day 1. The first thing is to generate the input:

WITH input AS (SELECT '199
200
208
210
200
207
240
269
260
263' AS input)

The Day 01 problem is kinda simple: you compare a row with the next one and when the next one is greater, you add 1 to a result. So in this case, we do need to split the input into numeric rows. There are two ways of doing this: you either first split then cast, or split and cast in a single query. The first one is easier, so let’s go with that:

WITH depths_as_str as (SELECT regexp_split_to_table(input.input, '\n') depth FROM input)
  , depths as (SELECT depth  :: integer FROM depths_as_str)

(more…)