Mull It Over
For Day 3, we’re given sequences of fictitious code and we have to extract the multiplication commands from it; which will be of the form “mul(x,y)” where x and y are integers.
So, using a regular expression makes finding the valid substrings easy. Once those are extracted, it’s just a matter of extracting the two numeric parameters from within the parentheses, multiplying them, and then summing the products.
For the sample data, there are just 4 valid multiplication commands which yield a summed-product of 161.
WITH
muls
AS
(SELECT str, REGEXP_SUBSTR(str, 'mul\([0-9]+,[0-9]+\)', 1, COLUMN_VALUE) x
FROM advent.data2rows('advent2024-3sample')
CROSS APPLY TABLE( SELECT COLLECT(LEVEL)
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(str, 'mul\([0-9]+,[0-9]+\)')))
SELECT SUM(TO_NUMBER(SUBSTR(x, 5, INSTR(x, ',') - 5)) * TO_NUMBER(RTRIM(SUBSTR(x, INSTR(x, ',') + 1), ')'))) answer
FROM muls;
ANSWER
----------
161
For part 2, some of the multiplications are not supposed to be included in the sum. The presence of "do()" and "don't()" commands toggle when a multiplication should or should not be included and we start by including them until we reach the first "don't()" command.
For this, I extended my regular expression to find all three commands: mul(x,y), do(), and don't(). I maintain their order and use the LAST_VALUE function to find what the last do() or don't() function was prior to each multiplication. The first row will have a null toggle since there is no last value, so I default it to "do" with NVL.
WITH
muls
AS
(SELECT seq,
COLUMN_VALUE n,
REGEXP_SUBSTR(str, 'mul\([0-9]+,[0-9]+\)|don''t\(\)|do\(\)', 1, COLUMN_VALUE) x,
str
FROM advent.data2rows('advent2024-3sample2')
CROSS APPLY TABLE( SELECT COLLECT(LEVEL)
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(str, 'mul\([0-9]+,[0-9]+\)|don''t\(\)|do\(\)')))
SELECT seq,
n,
CASE
WHEN x LIKE 'mul%'
THEN
TO_NUMBER(SUBSTR(x, 5, INSTR(x, ',') - 5)) * TO_NUMBER(RTRIM(SUBSTR(x, INSTR(x, ',') + 1), ')'))
END val,
NVL(
LAST_VALUE(CASE WHEN x = 'do()' THEN 'do' WHEN x = 'don''t()' THEN 'dont' END IGNORE NULLS)
OVER (ORDER BY seq, n),
'do') toggle,
x,
str
FROM muls;
SEQ N VAL TOGGLE X STR
--- - --- ------ ---------- ----------------------------------------------------------------------------------------------------
1 1 8 do mul(2,4) xmul(2,4)&mul[3,7]!^don't()_mul(5,5)+mul(32,64](mul(11,8)undo()?mul(8,5))
1 2 dont don't() xmul(2,4)&mul[3,7]!^don't()_mul(5,5)+mul(32,64](mul(11,8)undo()?mul(8,5))
1 3 25 dont mul(5,5) xmul(2,4)&mul[3,7]!^don't()_mul(5,5)+mul(32,64](mul(11,8)undo()?mul(8,5))
1 4 88 dont mul(11,8) xmul(2,4)&mul[3,7]!^don't()_mul(5,5)+mul(32,64](mul(11,8)undo()?mul(8,5))
1 5 do do() xmul(2,4)&mul[3,7]!^don't()_mul(5,5)+mul(32,64](mul(11,8)undo()?mul(8,5))
1 6 40 do mul(8,5) xmul(2,4)&mul[3,7]!^don't()_mul(5,5)+mul(32,64](mul(11,8)undo()?mul(8,5))
Once I had that, then it's just a matter of only including the products that have the "do" toggle, and then sum those that remain.
WITH
muls
AS
(SELECT seq,
COLUMN_VALUE n,
REGEXP_SUBSTR(str, 'mul\([0-9]+,[0-9]+\)|don''t\(\)|do\(\)', 1, COLUMN_VALUE) x,
str
FROM advent.data2rows('advent2024-3sample2')
CROSS APPLY TABLE( SELECT COLLECT(LEVEL)
FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(str, 'mul\([0-9]+,[0-9]+\)|don''t\(\)|do\(\)')))
SELECT SUM(val)
FROM (SELECT seq, n, CASE WHEN toggle = 'do' THEN val END val, x, toggle, str
FROM (SELECT seq,
n,
CASE
WHEN x LIKE 'mul%'
THEN
TO_NUMBER(SUBSTR(x, 5, INSTR(x, ',') - 5))
* TO_NUMBER(RTRIM(SUBSTR(x, INSTR(x, ',') + 1), ')'))
END val,
NVL(
LAST_VALUE(CASE WHEN x = 'do()' THEN 'do' WHEN x = 'don''t()' THEN 'dont' END IGNORE NULLS)
OVER (ORDER BY seq, n),
'do') toggle,
x,
str
FROM muls));
SUM(VAL)
----------
48
I felt this one was easier than day 2 for solving with SQL but if you're not familiar with regular expressions and the LAST_VALUE analytic function, it would have required a more complex solution.