Walking Through Advent of Code Day 6

Today we’re going racing! Sadly, it’s so not F1 or NASCAR racing. Snail racing is more like it since we’re moving millimeters by the end, but at least we’re closer to getting snow back to the elves, so let’s go racing!

Given a few numbers that are times and current record distances, this actually doesn’t look too bad to work with. First, as always, we have to load our data into SQL Server. This time, I loaded all of it into one table.

I also created a much smaller tally table than I was working with yesterday – amazingly I called it, SmallTally.

;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
     ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
		
     ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
     ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
     ,Tally (n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM lv3)
SELECT n
INTO SmallTally
FROM Tally 

From there, I can do yet another loop to go through all of the data points we have to play with.

WITH Time AS (	
SELECT TL.Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
		FROM (SELECT TheList FROM AocDay6 WHERE TheList like 'Time%' ) S
		CROSS APPLY STRING_SPLIT(TheList, ' ') TL),
	Distance AS (
		SELECT TL.Value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN
		FROM (SELECT TheList FROM AocDay6 WHERE TheList like 'Distance%' ) S
		CROSS APPLY STRING_SPLIT(TheList, ' ') TL)
SELECT T.value AS Time, D.value AS Distance, t.rn, 0 AS Times
INTO #TimeDist
FROM Time T
JOIN Distance D ON T.RN = D.RN;

DECLARE @Counter int,
        @TimeAllowed int, 
        @CurrentRecord int;

WHILE @Counter <= (SELECT COUNT(1) FROM #TimeDist)
BEGIN
        SELECT @TimeAllowed = Time, 
               @CurrentRecord = Distance
	FROM #TimeDist 
	WHERE RN = @Counter

	UPDATE #TimeDist
	SET Times = (	SELECT COUNT(1)
			FROM SmallTally 
			WHERE N < @TimeAllowed
			AND (@TimeAllowed - n) * n > @CurrentRecord	)
	WHERE RN = @Counter

	SET @Counter += 1
END   

As you’ll notice, in the #TimeDist temp table goes our list of times and Current Record Distances. From there, we’re quickly able to loop through our data and figure out the number of runs we can have that get further down the track than the current record holders.

Yes, that tally table quickly comes into good use in this example. Once we have all of the results in our Times field, we can multiply them all together and come out with the result needed to move on.

Until next time my friends when you know this will get trickier!