Walking Through Advent of Code Day 9

And so day 9 begins.

Today, our elves are working hard on having us come up with the next number in a sequence. Thankfully, they do give us a few good examples to walk through on how they would like us to do it. And with that, it’s off to the races!

As always, we load our input into our SQL Server. Today we will load it into the dbo.AOCDay9 table – mostly for consistency.

CREATE TABLE dbo.AOCDay9 (TheString varchar(200) NOT NULL);

We then need to create a temp table using this data – mostly so we can have a record of the row number. Is it crucial to be in the order in which we were provided the data? Not in this case, but it is an extremely recommended best practice to get into.

SELECT *, 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN,
    0 AS NewNumber
INTO #AOCDay9
FROM AOCDay9;

You’ll notice that I created a field called NewNumber as a placeholder for the number we will find for each row.

DROP TABLE IF EXISTS #OneNastyRow;

CREATE TABLE #OneNastyRow
(	RN int NOT NULL,
	History int NOT NULL,
	OrderNum int NOT NULL);

DECLARE @RowNumber int = 1,
	@Loops int = 1, 
	@Summation bigint;

I then created a temp table for each row of #AOCDay9 so that we could take one row from #AOCDay9 and do all of the work required to find the next number in sequence. I also created several variables for items like knowing what row number we are on, how many loops we have completed, and most importantly – knowing the sum of all of the numbers in a “row”.

WHILE @RowNumber <= (SELECT COUNT(1) FROM #AOCDay9)
BEGIN
    TRUNCATE TABLE #OneNastyRow

    SET @Loops = 1; -- Set the initial time for this one

    -- Get all of the row into our table in order
    -- Yes, here order does matter
    INSERT INTO #OneNastyRow
    SELECT @Loops, 
           nw.value AS History, 	
	   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS OrderNum
    FROM #aocDay9
    CROSS APPLY string_split(TheString, ' ') nw
    WHERE RN = @RowNumber;

    -- Push 0s into the row so that we can find where we start on the next row
    INSERT INTO #OneNastyRow
    SELECT @Loops, 0, 0

    -- Determine the sum of the last row we entered
    SELECT @Summation = SUM(CONVERT(bigint, History)) FROM #OneNastyRow WHERE RN = 1

    -- Making sure our Sum isn't 0.
    WHILE 0 <> @Summation
    BEGIN
       -- Comparing the last 2 rows and creating the next
        ;WITH Normal AS (SELECT CONVERT(bigint, History) AS History, OrderNum FROM #OneNastyRow WHERE OrderNum > 0 AND RN = @Loops - 1),
	       Previous AS (SELECT CONVERT(bigint, History) AS History, OrderNum + 1 AS OrderNum FROM #OneNastyRow WHERE RN = @Loops - 1)
	INSERT INTO #OneNastyRow
	SELECT @Loops, N.History - P.History, N.OrderNum - 1 AS OrderNum
	FROM Normal N 
	JOIN Previous P ON N.OrderNum = P.OrderNum
	WHERE N.OrderNum > 1;

	SET @Loops = @Loops + 1;

	SELECT @Summation = SUM(CONVERT(bigint, History)) FROM #OneNastyRow WHERE RN = @Loops - 1;

        -- If things go really wrong
	if @Loops > 25 BREAK;
    END

   -- Figure out history of max OrderNumber and add it to row before it's max
   -- Then put that answer in the NewNumber column
   ;WITH MaxOrderNum AS (
           SELECT RN, MAX(OrderNum) AS MaxOrderNum
	   FROM #OneNastyRow 
	   GROUP BY RN),
	SumMaxOrderNum AS (
	   SELECT SUM(History) AS SumMaxOrderNum 
	   FROM MaxOrderNum M
	   JOIN #OneNastyRow O ON O.RN = M.RN AND O.OrderNum = M.MaxOrderNum)
   UPDATE ac
   SET NewNumber = SN.SumMaxOrderNum
   FROM #AOCDay9 ac
   JOIN SumMaxOrderNum sn ON @RowNumber = Ac.RN

   -- Don't forget to go to the next row!
   SET @RowNumber = @RowNumber + 1
END
  

Several loops on this one. The outside loop works with the #AOCDay9 table and updates it as necessary. The inner loop only works with the #OneNastyRow table and inserts different data points into it.

Our final big update query gives us all of the data points on the far right-hand side of the table. Not the A or B data point, but the one previous to that.

0   3   6   9  12  15   B
  3   3   3   3   3   A
    0   0   0   0   0

In this case, we find the 3 and 15 – and add those up to get 18 – which is the answer we’re searching for for data point B.

After all of this work, all we need to do is sum up all of the new numbers we have found and we’re putting this puppy to bed!

Nothing new or fantastic in SQL Server development here – just churning out data as usual.

With that, I will leave you for today with a song based on sequences. Yes, that’s right, Nirvana’s Smells Like Teen Spirit’s main riff is based on a four-note sequence that descends by a whole step each time.