Advent of Code 2024 – Day 1

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.

My Advent of Code main page