I’m late getting started on the puzzles this year, I’ll try to catch up and see how many I can do before I break for the holidays and family time.
Secret Entrance
Part 1 was fairly obviously about modulo math; but the MOD function in Oracle works oddly with negative numbers. By most mathematical definitions -3 MOD 100 would be 97; but MOD(-3,100) will yield -3 with Oracle SQL, but MOD(-103,100) will also yield -3, so it is still modulo of a sort, but on a range of -99 to 99, not 0 to 99. This quirk isn’t a bug, it is documented functionality.
Fortunately, I didn’t need MOD to work like classical math definition I just needed to sum up all of the left (negative) and right (positive) turns and see how many times I got zero. Oracle’s version of the MOD function worked just fine for this.
Since I need all of the intermediate results I use an analytic window on the SUM function which gives a running total. Then I just count the zeroes in that total.
WITH data
AS
(SELECT str, seq, CASE WHEN str LIKE 'L%' THEN -TO_NUMBER(SUBSTR(str, 2)) ELSE TO_NUMBER(SUBSTR(str, 2)) END a
FROM advent.data2rows('advent2025-1sample'))
SELECT COUNT(*)
FROM (SELECT str, a, MOD(50 + SUM(a) OVER (ORDER BY seq), 100) x FROM data)
WHERE x = 0;
For part 2, I might have over complicated it. First I extracted all of the full loops. If I go 732 clicks, then no matter where I start, I will cross zero 7 times. Then I build a running total of the remainders, so 732 would be 7 loops, with a remainder of 32. -732 would also be 7 loops, but with a remainder of -32.
Unlike in part 1, I wanted my results after each turn to be mapped on 0-99, so I had to implement classical modulo logic. I chose to do that by adding 100 to the result and then using MOD again on that (i.e. MOD(x+100,100) = x modulo 100). There are other ways to calculate the modulo, but I was already thinking about MOD, so I using it seemed natural.
To check if I cross zero, I use the LAG function to compare my current position to where I was on the previous turn. If I turned to the left (negative) and my current position is greater than my prior one, then I must have crossed 0 (e.g. 3-7 = -4 and -4 modulo 100 is 96. Similarly, if I turn to the right (positive) and my current position is less than my prior position then I must have crossed 0 (e.g. 96 + 7 = 103 and 103 modulo 100 is 3.) So, if either of those conditions happen then I count that as a zero-tick.
Then I sum all the loops and all the ticks to get my answer. The sample data doesn’t have loops but my full data did (and likely all other players do too.)
WITH data
AS
(SELECT str, seq, CASE WHEN str LIKE 'L%' THEN -TO_NUMBER(SUBSTR(str, 2)) ELSE TO_NUMBER(SUBSTR(str, 2)) END a
FROM advent.data2rows('advent2025-1sample'))
SELECT d.*, SUM(loops) OVER() + SUM(tick) OVER()
FROM (SELECT str,
seq,
a,
loops,
x,
y,
CASE WHEN (y != 0 AND a < 0 AND x > y) OR (a > 0 AND x < y) OR x = 0 THEN 1 END tick
FROM (SELECT str, seq, a, loops, x, NVL(LAG(x) OVER (ORDER BY seq), 50) y
FROM (SELECT str,
seq,
a,
FLOOR(ABS(a) / 100) loops,
MOD(MOD(50 + SUM(MOD(a, 100)) OVER (ORDER BY seq), 100) + 100, 100) x
FROM data))) d;
I'll try to get a few more done tomorrow and over the weekend and hopefully catch up, but posting a few days late also helps that I'm not posting spoilers on the day of each puzzle.
I hope these help you tackle similar modulo problems and remind that MOD function might work differently than you're expecting.