Advent of Code 2024 in SQLite: Day 2

On Day 2 the input was a list of integer sequences, like this:

16 19 21 24 21
15 18 19 22 24 25 25
80 81 83 84 87 89 93
6 7 8 9 10 13 18
60 62 61 64 66 67
76 79 81 84 82 80

In the first part of Day 2 the task was to find the rows where:

• The values are all increasing or all decreasing
• Any two adjacent values differ by at least one and at most three

And then report the number of rows that met these conditions.

I used a regex to parse the input.

create table T (row text);
.import 02_input.txt T
.load ./regex0.so

-- P contains the problem input
--
-- r is the row index
-- c is the column index
-- v is the value at (r, c)
with P(r, c, v) as (
	select
		T.rowid,
		M.rowid,
		cast(M.match as integer)
	from
		T,
		regex_find_all("\d+", T.row) as M
)

Then I used the window function lag to get a table containing the adjacent items.

-- L contains all adjacent pairs
-- of values for each row
--
-- r is the row
-- v1 is the first value of the pair
-- v2 is the second value of the pair
L(r, v1, v2) as (
	select
		r,
		v,
		lag(v) over (partition by r order by c)
	from P
)

Yielding a result like this:

┌───┬──────┬──────┐
│ r │ pred │ succ │
├───┼──────┼──────┤
│ 1 │ 16   │      │
│ 1 │ 19   │ 16   │
│ 1 │ 21   │ 19   │
│ 1 │ 24   │ 21   │
│ 1 │ 21   │ 24   │
│ 2 │ 15   │      │
│ 2 │ 18   │ 15   │
│ 2 │ 19   │ 18   │
│ 2 │ 22   │ 19   │
└───┴──────┴──────┘

In Postgres we would have reached for bool_and to aggregate the result now, but SQLite does not have any functions for aggregating booleans. So instead we need to use sum and exploit the fact that booleans can be coerced into (0, 1).

select count(*)
from (
    select r
    from L
    group by r
    having
        sum(abs(pred - succ) > 3) = 0
        and (sum(pred <= succ) = 0 or sum(pred >= succ) = 0)
)

In Part 2 it was allowed to discard any value from the row. That is, if the input was 1 2 3 1 6, we were allowed to discard the second 1 if that made the row meet the criteria. Just like in Part 1 we should then report how many rows meet the criteria.

The solution for Part 2 was very similar to the solution for Part 1, but I added an additional join in the L-table to discard columns.

-- D contains all the columns indices
-- that we will discard
--
-- dr is the row index
-- dc is the column index to discard
D(dr, dc) as (
	select r, c from P
),

-- L contains all adjacent pairs
-- of values partitioned by row and
-- the index of the discarded column
--
-- r is the row index
-- dc is the discarded column
-- pred is the first value of the pair
-- succ is the second value of the pair
L(r, dc, pred, succ) as (
	select
		r,
		dc,
		v,
		lag(v) over (partition by r, dc order by c)
	from P
	join D on r = dr
	where c != dc
)

select count(distinct r)
from (
    select r
    from L
    group by r, dc
    having
        sum(abs(pred - succ) > 3) = 0
        and (sum(pred <= succ) = 0 or sum(pred >= succ) = 0)
)

The runnable scripts can be found on GitHub.