Advent of Code 2024 in SQLite: Day 1

On Day 1 the input was two lists of integers of equal length.

The input was formatted like this:

3   4
4   3
2   5
1   3
3   9
3   3

I used a regex to parse the input:

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

-- P contains the problem input
--
-- a is the first value of the row
-- b is the second value of the row
with P(a, b) as (
	select
		regex_capture(captures, 1),
		regex_capture(captures, 2)
	from
		regex_captures('(\d+)\s+(\d+)', T.row)
	join T
)

In the first part of Day 1 the task was to sort the lists and then subtract the items in the second list from the items in the first list (like zipWith (-) in Haskell) and then sum the results. This was quite straightforward to express in SQL:

select sum(abs(a-b))
from (
	select a from P order by a
) as P1
join (
	select b from P order by b
) as P2
on P1.rowid = P2.rowid

In the second part the task was to count the number of times an item in the left list occurred in the right list and use the count as a multiplier. I used aggregation on the second row value to achieve this:

select sum(a*n)
from (
	select b, count(*) as n
	from P
	group by b
) as N
join P on N.b = P.a

The runnable scripts can be found on GitHub.