After Day 3, I’m glad at least some of you made it back for Day 4.
Today, we get to work with scratch-offs and a lack of getting the right winning numbers on the right cards. Sounds like a normal Thursday night for me…
Now we get to work with a set of numbers that our elf played and a set of winning numbers for each card. This sounds like something that SQL is meant for!
As always, we load the fun input data provided by the AoC group into our database. I actually loaded it in as 2 columns – Card and String. I’m just not a fan of throwing all of our data into one column of a table and letting it all get sorted out later.
Once we have our 2 columns loaded in, we’re going to split our column String into 2 more columns – NumbersWeHave and WinningNumbers.
ALTER TABLE aocDay4
ADD NumbersWeHave varchar(100) NULL,
WinningNumbers varchar(150) NULL;
UPDATE aocDay4
SET NumbersWeHave = TRIM(LEFT(String, 31)),
WinningNumbers = TRIM(RIGHT(String, LEN(String) - 32))Now, I’m sure some of you are wondering what the significance of the numbers 31 and 32 are in the above code. It’s simply the placement of the “|” character in the table. As always, you should double-check where the character is in your input before running code helter-skelter in your database.
This time, we’re going to do something that we haven’t gotten to do a lot this whole AoC – do a large portion of work with one query.
SELECT Card, nw.value AS AWinningNumber
INTO #TheWinners
FROM aocDay4
CROSS APPLY string_split(NumbersWeHave, ' ') nw
CROSS APPLY string_split(WinningNumbers, ' ') wn
WHERE nw.value <> ''
AND wn.value <> ''
AND nw.value = wn.value;
If you’ve never had the pleasure of working with the STRING_SPLIT function or the CROSS APPLY operator – I would suggest doing so right away. I talked about the STRING_SPLIT function here and the CROSS APPLY with the STRING_SPLIT here.
What the above code does is to split out all of the numbers in the NumbersWeHave column into a row by itself. Add to that each row has every number from the WinningNumbers in a row by itself! So, now we just have to grab those rows where a number from one column matches what is the other!
You’ll also notice the part in the WHERE clause about nw.value <> ” and wn.value <> ”. I placed that there because, at least in my input data, several numbers are only 1 digit, instead of 2 digits. Since the input data had the single digit numbers in our NumbersWeHave column as a blank space followed by the digit, the STRING_SPLIT function spits the blank character out as a new row. We do have to take it out of our result set or we will have more results than we really should – which would be bad.
After this, I was able to place 2 more columns into the original input data – in my case, a table named aocDay4.
ALTER TABLE AocDay4
ADD Winners int NULL,
Points int NULL
;
;with cte AS
( SELECT Card, COUNT(1) AS TheCount
FROM #TheWinners
GROUP BY Card)
UPDATE a
SET Winners = c.TheCount
FROM cte c
JOIN aocDay4 a on a.Card = c.Card
I then updated my new Winners column with the count of how many rows we had in the #TheWinners table for each card.
Figuring out the formula for determining the points is above and beyond since we can just write update statements for each of the ones that matched. The formula is a geometric progression that is fairly common. The formula can be shown as:
With that, you’ll see that it 2 to the power of (n-1). In other languages, getting that value is 2^(n-1). Well, guess what? SQL doesn’t use that.
UPDATE aocDay4 SET Points = POWER(2, Winners - 1);Yes, that is the POWER function. Not a very commonly used function (at least outside of scientific and engineering circles) but very very helpful when you do need it.
At this point, you have all of the points for each card. Now, you can sum them all up and provide them to the group!
With that, we’ve come to the end of yet another part of the #AoC2023 fun. Today, we’ve talked through the use of STRING_SPLIT, CROSS APPLY, and POWER. Hopefully, you learned some goodness with all of those!