Advent of Code in SQL – Day 01
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)