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.
Second challenge
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!