Walking Through Advent of Code Day 7 Part 2

So, we got tired of playing Camel Cards and decided to throw some Jokers into the mix.

This time, our wonderful friends the elves have decided that they don’t like Jacks but they do like Jokers. So, there are a few changes to our code that will need to be made:

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( CASE WHEN z.Value = 'J' THEN NULL ELSE z.TheCount END) AS MaxCount, 
			MIN( CASE WHEN z.Value = 'J' THEN NULL ELSE NULLIF(TheCount,0) END) AS MinCount, 
			COUNT( CASE WHEN z.Value = 'J' THEN NULL ELSE 1 END) 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;

For our wonderful #Hands table, we made a few small changes. Within the AggregateData cte, all of our aggregate functions needed to be updated to make the Joker’s not count towards our totals. And as you can tell, that’s really it here. No large changes, but they do make a difference.

-- Setup what kinds of hands we have
UPDATE #Hands SET TypeOfHand = 6 WHERE ISNULL(HighestCount, 0) + [J] = 5;			-- Five Of A Kind
UPDATE #Hands SET TypeOfHand = 5 WHERE HighestCount + [J] = 4;					-- Four of a Kind
UPDATE #Hands SET TypeOfHand = 4 WHERE HighestCount = 3 AND LowestCount = 2 AND WorkingString NOT LIKE '%J%';		-- Full House
UPDATE #Hands SET TypeOfHand = 4 WHERE HighestCount = 2 AND DiffCards = 2 AND WorkingString LIKE '%J%';			-- Full House
UPDATE #Hands SET TypeOfHand = 3 WHERE HighestCount = 3 AND TypeOfHand = 0 AND WorkingString NOT LIKE '%J%';	        -- Three of a Kind
UPDATE #Hands SET TypeOfHand = 3 WHERE HighestCount + [J] = 3 AND TypeOfHand = 0 AND WorkingString LIKE '%J%';	        -- Three of a Kind
UPDATE #Hands SET TypeOfHand = 2 WHERE HighestCount = 2 AND DiffCards = 3 AND WorkingString NOT LIKE '%J%';		-- Two Pair
UPDATE #Hands SET TypeOfHand = 1 WHERE HighestCount + [J] = 2 AND TypeOfHand = 0;	-- One Pair
UPDATE #Hands SET TypeOfHand = 0 WHERE DiffCards = 5;										-- High Card
 

There are slight changes in logic for this portion. Notice that for the Five of a Kind, we added an ISNULL function call. At least for my input, I needed to because there was one hand – JJJJJ. As we all know NULL + 5 is still NULL, so we slightly modified this.

For the Full House and Three of a Kind types, I was forced to break them into 2 different update statements because they work slightly differently for each group.

Also, note that there is no corresponding “LIKE ‘%J%'” for the Two Pair query. This is because there cannot be Two Pairs with a Joker. Having a Joker automatically bumps a single pair to a Three of a Kind so none of that type will be in the list with Jokers.

In the creation of the #RankedAndStacked table, I made one slight change to the CardRank cte:

SELECT SpotValue, SpotSymbol
FROM (values ('2',2),('3',3),('4',4),('5',5),('6',6)
              ,('7',7),('8',8),('9',9),('T',10)
              ,('J',1),('Q',12),('K',13),('A',14)) Spots(SpotSymbol,SpotValue))  

Notice that the ‘J’ is now worth only 1 point instead of the original 11. It just shows that my idea of putting the ‘2’ to be worth 2 points is even smarter.

And that, my friends, is the end of yet another successful day of #AdventOfCode. Great logic puzzle today – getting all of the logic correct was definitely the hardest part of today’s puzzle.

Since we are now past Christmas, I’ll start back with more fun videos – since I know most people are tired of Advent songs at this point. Today’s is appropriate for the exercise: