⇠ Back to Posts

Advent of Code

Working again on Advent of Code puzzles. The puzzles again suggested data processing to me, so again I cooked up SQL solutions.

Submarine Pilot

This puzzle is a sequence of values describing motion in two dimensions, a good twist on our previous single scalar variation. With a little find and replace, I prettied up the values into two columns for insert, the direction and the scalar.

CREATE TABLE #instructions 
(rowNumber BIGINT IDENTITY, direction VARCHAR(100), scalar BIGINT)

INSERT INTO #instructions (direction, scalar)
SELECT direction, scalar
FROM (
    VALUES ('forward',6),
        ('down',2),
        ('forward',2),
        ('down',8),
        ...
        ('forward',8)
) t(direction, scalar);

Again using the Common Table Expression (CTE) and the case statements allows for separate data streams (columns) and a little preprocessing for up versus down so that we can just straight sum things. Technically the answer is the two values multiplied together but that was beautiful and easy too with SELECT 1895 * 1067 to do my math for me.

WITH cte AS (
    SELECT 
        CASE WHEN direction = 'forward' THEN scalar ELSE NULL END AS forward,
        CASE 
            WHEN direction = 'up' THEN scalar * -1
            WHEN direction = 'down' THEN scalar
        END AS depth
    FROM #instructions
)
SELECT sum(forward) AS hDist, sum(depth) AS vdist
FROM cte

But, of course, that was only puzzle number one and it turns out everything was wrong!

Twist Again

Luckily, the second puzzle isn’t that much more difficult than the first. It’s another turn of the screw on the theme.

WITH cte AS (
    SELECT rowNumber,
        CASE WHEN direction = 'forward' THEN scalar ELSE NULL END AS forward,
        CASE 
            WHEN direction = 'up' THEN scalar * -1
            WHEN direction = 'down' THEN scalar
        END AS aim
    FROM #instructions
)
, cte2 AS (
    SELECT rowNumber, forward, aim,
        sum(forward) OVER(ORDER BY rowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS hDist, 
        coalesce(sum(aim) OVER(ORDER BY rowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0) AS runningAim
    FROM cte
)
SELECT *, 
    forward * runningAim AS depthChange, 
    sum(forward * runningAim) OVER(ORDER BY rowNumber ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS runningDepth
FROM cte2

In this case, we keep the first cte the same, sorting out our data streams into axes and multiplying by negative one for motion along the axis in the opposite direction. The second CTE is really just the outer select from the previous puzzle, the reason it looks different is that I wanted to be able to see the row by row values for sanity checking. The outer select then just carries forward my hDist values from the second CTE, but the further calculations of multiplying the motion by the aim and setting up that running total for the depth get worked in finally. And, again, the last row has my values to multiply together to get my answer.

I’m definitely curious what the next puzzle looks like to see whether this trend continues or something different crops up.

⇠ Advent of Code

New Puzzle ⇢