Historian Hysteria
In this puzzle we’re given 2 lists of numbers that should be paired together such that the smallest value of each list form a pair, then the next smallest, and so on until we pair the largest values from each list together.
To solve it, I read the values from each line of the input file, parsing the two numbers into A and B columns corresponding to the values of the left and right lists respectively. Then using ROW_NUMBER function, I give an ordering to each A value and each B value.
Then, it’s simply a matter of joining the on the ordering values so that 1=1, 2=2, 3=3, and so on. That creates my ordered pairs.
To get the final result, I sum the difference of the two. It doesn’t matter if A or B is larger, we only care about the magnitude of the difference so I sum the absolute value of the difference. With the small example, provided you can see the sum of the differences is 11 as expected.
WITH
data
AS
(SELECT TO_NUMBER(REGEXP_SUBSTR(str, '^[0-9]+')) a, TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+$')) b
FROM advent.data2rows('advent2024-1sample')),
a AS(SELECT a, ROW_NUMBER() OVER (ORDER BY a) rn FROM data),
b AS(SELECT b, ROW_NUMBER() OVER (ORDER BY b) rn FROM data)
SELECT a, b, ABS(a - b), SUM(ABS(a - b)) OVER() answer
FROM a, b
WHERE a.rn = b.rn;
A B ABS(A-B) ANSWER
---------- ---------- ---------- ----------
1 3 2 11
2 3 1 11
3 3 0 11
3 4 1 11
3 5 2 11
4 9 5 11
In part 2, we use the same two lists of values, but instead of pairing them from lowest to highest, we count how many times each value from the A-list appears in the B-list.
This starts with the same splitting I used in part 1, but this time instead of joining the two lists based on the ordering, I use a scalar subquery to count the B values for each A value.
Then, I multiply each A value by its corresponding count, and sum those products. Using the sample data again, we see we get the expected answer of 31.
WITH
data
AS
(SELECT TO_NUMBER(REGEXP_SUBSTR(str, '^[0-9]+')) a, TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+$')) b
FROM advent.data2rows('advent2024-1sample'))
SELECT a, cnt, SUM(a * cnt) OVER() answer
FROM (
SELECT a,
(
SELECT COUNT(*)
FROM data b
WHERE a.a = b.b
) cnt
FROM data a
);
A CNT ANSWER
---------- ---------- ----------
3 3 31
4 1 31
2 0 31
1 0 31
3 3 31
3 3 31
As expected, day 1 wasn’t too hard. They definitely got more complex as the days went by.