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)
For people unfamiliar with “modern-ish SQL”, we have something called “window functions”. These are functions that operate not only at the current row, but also on previous and/or next ones. Unfortunately, we only have
SUM... as an aggregate function, and not
SUB to aggregate by subtracting everyone, so we’ll have to use
NTH_VALUE to get the second value that our window function returns (the next ROW) and subtract it with the current one. So let’s do that:
differences as ( SELECT NTH_VALUE(depth, 2) OVER (ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) - depth AS diff FROM depths )
depths here is the table with all the numbers already splitted. So I’m aggregating
depth (the only field on
depths) with an window function – that is, I want the second value (literally the second –
NTH_VALUE is 1-based, not 0-based as we’re used to in other languages)
OVER a group of rows:
0 PRECEDING (that is, it starts on the current row)
AND 1 FOLLOWING (that is, the next row in the table). I subtract the second value with the current value of
depth, and that is the difference. To get the final result, I just need to:
SELECT COUNT(diff) sums FROM differences WHERE diff > 0
Shouldn’t be too hard to undestand what’s happening here.
The second challenge asks us to do the same, but we have to
SUM 3 adjacent rows to get the result. This means, yes, another window function:
windows AS ( SELECT SUM(depth) OVER (ROWS 2 PRECEDING) AS depth FROM depths)
That was quite simple – sum 3 rows, the current one and
2 PRECEDING to get the new table
windows. Then the code is LITERALLY the same, but because SQL is the way it is, we can’t re-use the query: we have to copy-paste the original query just changing
FROM depths to
FROM windows. But that gave us the final result:
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) , differences as ( SELECT NTH_VALUE(depth, 2) OVER (ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) - depth AS diff FROM depths) , windows as ( SELECT SUM(depth) OVER (ROWS 2 PRECEDING) AS depth FROM depths) , win_diffs as ( SELECT NTH_VALUE(depth, 2) OVER (ROWS BETWEEN 0 PRECEDING AND 1 FOLLOWING) - depth AS diff FROM (SELECT * FROM windows OFFSET 2) w) SELECT COUNT(diff) sums FROM differences WHERE diff > 0 UNION ALL SELECT COUNT(diff) sums FROM win_diffs WHERE diff > 0;
In the end, I decided to do an
UNION ALL to get both the first and the second answer together in a table.
Bonus points: performance
In my current machine and with the dataset they sent me, it takes 8.7ms to calculate everything. It’s slower that Babashka solution, with 2.5ms, but I think most of the slowdown is because of the creation of the tables. On more resource-intensive solutions, it’ll probably be better. We’ll see!