Historian Hysteria
On this puzzle we’re given 2 lists of numbers that must be paired collectively such that the smallest worth of every checklist type a pair, then the following smallest, and so forth till we pair the biggest values from every checklist collectively.
To resolve it, I learn the values from every line of the enter file, parsing the 2 numbers into A and B columns akin to the values of the left and proper lists respectively. Then utilizing ROW_NUMBER operate, I give an ordering to every A price and every B worth.
Then, it’s merely a matter of becoming a member of the on the ordering values in order that 1=1, 2=2, 3=3, and so forth. That creates my ordered pairs.
To get the ultimate consequence, I sum the distinction of the 2. It doesn’t matter if A or B is bigger, we solely care in regards to the magnitude of the distinction so I sum absolutely the worth of the distinction. With the small instance, offered you’ll be able to see the sum of the variations is 11 as anticipated.
WITH
knowledge
AS
(SELECT TO_NUMBER(REGEXP_SUBSTR(str, '^[0-9]+')) a, TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+$')) b
FROM introduction.data2rows('advent2024-1sample')),
a AS(SELECT a, ROW_NUMBER() OVER (ORDER BY a) rn FROM knowledge),
b AS(SELECT b, ROW_NUMBER() OVER (ORDER BY b) rn FROM knowledge)
SELECT a, b, ABS(a - b), SUM(ABS(a - b)) OVER() reply
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
Partly 2, we use the identical two lists of values, however as an alternative of pairing them from lowest to highest, we rely what number of occasions every worth from the A-list seems within the B-list.
This begins with the identical splitting I utilized in half 1, however this time as an alternative of becoming a member of the 2 lists based mostly on the ordering, I take advantage of a scalar subquery to rely the B values for every A price.
Then, I multiply every A price by its corresponding rely, and sum these merchandise. Utilizing the pattern knowledge once more, we see we get the anticipated reply of 31.
WITH
knowledge
AS
(SELECT TO_NUMBER(REGEXP_SUBSTR(str, '^[0-9]+')) a, TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+$')) b
FROM introduction.data2rows('advent2024-1sample'))
SELECT a, cnt, SUM(a * cnt) OVER() reply
FROM (
SELECT a,
(
SELECT COUNT(*)
FROM knowledge b
WHERE a.a = b.b
) cnt
FROM knowledge a
);
A CNT ANSWER
---------- ---------- ----------
3 3 31
4 1 31
2 0 31
1 0 31
3 3 31
3 3 31
As anticipated, day 1 wasn’t too laborious. They positively acquired extra complicated as the times glided by.
