Walking Through Advent Of Code Day 7

No longer are we racing… Now, we’re playing cards. Not just any cards though. This time we’re playing Camel Cards – if you get the gist of poker, you can get Camel Cards fairly quickly.

As always, have some fun trying different ways to push the data into your SQL Server. Why not try using Powershell, bcp, or even Python – just to mix it up a bit and maybe learn a few things?

I have my version of the Day 7 data in my handy dandy table called AOCDay7. This time we’re being asked to figure out what kinds of Camel hands are given to us and then told to rank all of them.

Let’s start by aggregating and analyzing the data a little bit.

with cte as (
	SELECT SUBSTRING(TheString, 1, 1) + ' ' +
		SUBSTRING(TheString, 2, 1) + ' ' +
		SUBSTRING(TheString, 3, 1) + ' ' +
		SUBSTRING(TheString, 4, 1) + ' ' +
		SUBSTRING(TheString, 5, 1)  AS StringWithSpace,
		TheString
	FROM AOCDay7),
	GroupingData AS (
		SELECT t.TheString, tl.value
		FROM cte t 
		CROSS APPLY STRING_SPLIT(t.StringWithSpace, ' ') tl	),
	AggregateData AS (
		SELECT TheString, MAX(TheCount) AS MaxCount, MIN(NULLIF(TheCount,0)) AS MinCount, COUNT(1) AS DiffCards
		FROM (SELECT TheString, value, COUNT(1) AS TheCount 
			FROM GroupingData
			GROUP BY TheString, value) Z
		GROUP BY TheString)
SELECT p.WorkingString, [A], [K], [Q], [J], [T], [9], [8], [7], [6], [5], [4], [3], [2], 
	   MAXCount AS HighestCount, MinCount AS LowestCount, DiffCards, 0 AS TypeOfHand
INTO #Hands
FROM (SELECT GD.TheString AS WorkingString, MMD.MaxCount, MMD.MinCount, MMD.DiffCards, GD.* 
		FROM GroupingData GD
		JOIN AggregateData MMD ON MMD.TheString = GD.TheString) U
PIVOT (COUNT(u.TheString) FOR value in 
	([A], [K], [Q], [J], [T], [9], [8], [7], [6], [5], [4], [3], [2]) ) P; 

So much going on here! In the cte named “cte” – I told you I was a master at naming things – we’re actually putting a space after each character by using the SUBSTRING function and the old-school concatenation operator. In the GroupingData cte, I used our friends CROSS APPLY and STRING_SPLIT to split each of the cards into a separate row.

Now, I’m sure you’re thinking – why not just use STRING_SPLIT with a zero-length string and be done? Sadly, the STRING_SPLIT function doesn’t allow for that action. Hence, I had to add a space – or some type of character – between each card. And thankfully, all of the hands are 5 cards so I knew exactly how many characters would have to be worked with here.

In the next cte, named AggregateData, I determined how many types of cards there were in each hand, as well as, which card type had the most cards and the least. Most of those are self-explanatory, but the least card type is a bit different. Normally, we would just use the MIN function, but we don’t want to see a zero (0) as an answer, therefore we used the NULLIF function to make zeros be seen as NULL to the MIN function and let SQL Server do the work from there.

In the main part of our query, we got to PIVOT – one of my favorite functions. This lets us easily determine how many of each card are available, as well as, giving us all of the aggregated data we determined earlier. Notice that we defaulted the TypeOfHand to 0. I did this so that the #Hands temp table will have the field as an integer data type. I could have defaulted it to 200 – to make it very obvious that I haven’t done anything with that value as of yet.

Next, I want to set the TypeOfHand to a number that corresponds with the type of hand that was dealt:

-- Setup what kinds of hands we have
UPDATE #Hands SET TypeOfHand = 6 WHERE HighestCount = 5;			-- Five Of A Kind
UPDATE #Hands SET TypeOfHand = 5 WHERE HighestCount = 4;			-- Four of a Kind
UPDATE #Hands SET TypeOfHand = 4 WHERE HighestCount = 3 AND LowestCount = 2;	-- Full House
UPDATE #Hands SET TypeOfHand = 3 WHERE HighestCount = 3 AND TypeOfHand = 0;	-- Three of a Kind
UPDATE #Hands SET TypeOfHand = 2 WHERE HighestCount = 2 AND DiffCards = 3;	-- Two Pair
UPDATE #Hands SET TypeOfHand = 1 WHERE HighestCount = 2 AND TypeOfHand = 0;	-- One Pair
UPDATE #Hands SET TypeOfHand = 0 WHERE DiffCards = 5;				-- High Card
 

Notice that the types of hands are done in a specific order. The Full House hands are determined before Three of a Kind. The same can be said for Two Pair and One Pair. This was done truly to make the logic a little simpler to follow.

WITH CardRank AS (
   SELECT SpotValue, SpotSymbol
   FROM (values ('2',2),('3',3),('4',4),('5',5),('6',6)
              ,('7',7),('8',8),('9',9),('T',10)
              ,('J',11),('Q',12),('K',13),('A',14)) Spots(SpotSymbol,SpotValue))
SELECT A7.TheString, A7.Bet 
	,DENSE_RANK() OVER (ORDER BY TypeOfHand) AS TypeOfHandOrder
	,CR1.SpotValue AS Char1Order
	,CR2.SpotValue AS Char2Order
	,CR3.SpotValue AS Char3Order
	,CR4.SpotValue AS Char4Order
	,CR5.SpotValue AS Char5Order
INTO #RankedAndStacked
FROM #Hands H
JOIN AOCDay7 A7 ON A7.TheString = H.WorkingString
JOIN CardRank CR1 ON CR1.SpotSymbol = LEFT(A7.TheString, 1)
JOIN CardRank CR2 ON CR2.SpotSymbol = SUBSTRING(A7.TheString, 2, 1)
JOIN CardRank CR3 ON CR3.SpotSymbol = SUBSTRING(A7.TheString, 3, 1)
JOIN CardRank CR4 ON CR4.SpotSymbol = SUBSTRING(A7.TheString, 4, 1)
JOIN CardRank CR5 ON CR5.SpotSymbol = SUBSTRING(A7.TheString, 5, 1)

With our CardRank cte, I did something you don’t see very often, but can be useful. I used the VALUES function to explicitly state the values I wanted to use. I could have used another temp table, but since this is only used in this query, this seemed a more logical way to go. Since there is a very specific order to the cards, I gave each card a number to be associated with. Yes, you’ll notice it does not start at 1 – nothing says you have to, so I didn’t.

For the TypeOfHandOrder field, I used the DENSE_RANK windowing function so that each type of hand is grouped together. I could have used the RANK windowing function but I wanted it to go in order without skips (No real reason that it has to be DENSE_RANK but it does make some logical sense to go 0 – 6 instead of skipping huge blocks of numbers.)

I also joined to the CardRank cte several times because I needed to determine the order for each card that was in the hand – card by card. I try not to use functions in the JOIN clause but this is such a small set of data I didn’t mind this time…

And finally, we get to the final query that gives us the answer we’ve all been waiting for:

;with DataGoodness AS (
	SELECT TheString, 
               Bet,
	       ROW_NUMBER() OVER (ORDER BY TypeOfHandOrder, Char1Order, Char2Order, Char3Order, Char4Order, Char5Order) AS CardRank
	FROM #RankedAndStacked)
SELECT SUM(Bet * CardRank)
FROM DataGoodness 

With the ROW_NUMBER windowing function, we determine the rank for each hand with the rules that the elves have given us. Then we provide the large number to the system and Bam! Another gold star is added to our collection!

Hopefully, you’ve been playing along and are learning some great functions that can be used quite often in SQL Server. Join us again when we work through Part 2 of Day 7! Until next time my friends!