⇠ Back to Posts

Reloaded

I was avoiding puzzle two of Day Four because I got stuck on how to get at the last board to have the first column or row filled. I’ve had a bit to think about it and found that I really just needed to do the RBAR processing by the board and get it over with. Only 100 boards anyway.

Query

The query is a little ugly, but mostly just brute force and some rearrangement of the data. Five inner joins later, a group by, five max calculations, five unioned tables, and a min calculation, and we have an answer per board for the first row or column to win on the board and the sequence number it wins on.

CREATE TABLE #BoardFirstWinSequence (board BIGINT, val BIGINT)

DECLARE @board BIGINT = 0;

WHILE @board < 100
BEGIN
	WITH maxSeqForRowOrColumnToWin AS (
		SELECT @board AS board,
			b.RowOrCol, 
			MAX(sb.rowNumber) AS b_LastSeqWin, 
			MAX(si.rowNumber) AS i_LastSeqWin, 
			MAX(sn.rowNumber) AS n_LastSeqWin, 
			MAX(sg.rowNumber) AS g_LastSeqWin, 
			MAX(so.rowNumber) AS o_LastSeqWin
		FROM BingoRowsAndColumns b
		INNER JOIN ##sequence sb ON b.b = sb.val
		INNER JOIN ##sequence si ON b.i = si.val
		INNER JOIN ##sequence sn ON b.n = sn.val
		INNER JOIN ##sequence sg ON b.g = sg.val
		INNER JOIN ##sequence so ON b.o = so.val
		WHERE board = @board
		GROUP BY b.RowOrCol
	)
	INSERT INTO #BoardFirstWinSequence (board, val)
	SELECT @board AS board, MIN(maxSeqForRowOrColumnToWin) AS FirstRowOrColumnToWin
	FROM (
		SELECT b_LastSeqWin FROM maxSeqForRowOrColumnToWin
		UNION
		SELECT i_LastSeqWin FROM maxSeqForRowOrColumnToWin
		UNION
		SELECT n_LastSeqWin FROM maxSeqForRowOrColumnToWin
		UNION
		SELECT g_LastSeqWin FROM maxSeqForRowOrColumnToWin
		UNION
		SELECT o_LastSeqWin FROM maxSeqForRowOrColumnToWin
	) t(maxSeqForRowOrColumnToWin)

	SELECT @board = @board + 1;
END

SELECT *
FROM #BoardFirstWinSequence
ORDER BY val

You can see how to get the answer out of the board in my previous post on puzzle one.

Thinking

I’m having a ton of fun with the 2021 Advent of Code puzzles. I’m also a bit excited about putting up these solutions so that I can point to them later when I want to give people examples of ways to do things in SQL that they might not be thinking of, or that they want to avoid!

⇠ Crab Alignment

Bad Wiring ⇢