Walking Through Advent of Code Day 4 Part 2

Day 4 part 1 worked very smoothly. Let’s see what part 2 holds in store for us.

With part 1, we just had to figure out how many times each of the winners showed up. With part 2 though, we have to jump through several hoops since we now have to determine how many cards we will end up with if we win the next series of cards for every match.

I slimmed down the table that I’m working with to only 2 columns – yes, in the real world, I would have used a view, but today was not that day.

CREATE TABLE Day4Cards
(	Card varchar(20) NOT NULL,
	Winners int NOT NULL,
	CardsToMake varchar(100) NULL);

INSERT INTO Day4Cards (Card, Winners)
SELECT LTRIM(RTRIM(REPLACE(Card, 'Card ', ''))), 
       Winners
FROM aocDay4

Now that we have our smaller table and we’ve pushed our older data into it, you should notice that I have a new field – called CardsToMake. In this field, I want to create a list of what cards are won by each card in this new scenario. Sadly, this must be a character string so get ready for some string parsing fun!

declare @i int = 1, 
        @ResultString VARCHAR(MAX), 
        @CardIteration INT, 
        @StoppingCard int,
       	@NumberOfLines int;

-- Since it is possible you will have a different number of lines than I do 
SELECT @NumberOfLines = COUNT(1) FROM Day4Cards;


While @i <= @NumberOfLines 
begin
        -- Reset our Results and what card # we're looking for each time
	select @ResultString = '',
	       @CardIteration = @i + 1
	
        -- Determine what number we'll stop at
	select @StoppingCard = @i + Winners FROM Day4Cards where Card = @i;

        -- loop thru and create the overall string
	while @CardIteration <= @StoppingCard
	begin
	    SET @ResultString = @ResultString + CAST(@CardIteration AS VARCHAR) + ' ';
	    set @CardIteration = @CardIteration + 1
	end

        -- set the one row we care about this loop and iterate our index
	update Day4Cards set CardsToMake = RTRIM(@ResultString) Where Card = @i;
	set @i = @i + 1;
end

Hopefully, my code comments make the code more helpful in what each section is doing. As always, please ensure that you are commenting often in your own code (and not with unhelpful comments like “This query updates the main table.) Try to be helpful to yourself six months from now when you have to look back at your code to troubleshoot some issue that has arisen.

Also, try to come up with decent variable names. i or j is fine for loops, but please don’t use one-letter variable names for almost anything else. You don’t have to be overly verbose, e.g. OverlyLongNamesThatNoOneCanTypeWithoutUsingIntellisense, but you can make it somewhat easier on yourself to name variables with what data they should have in them.

Now that we have our list of what each card will win, we can finally start figuring out how many cards we are going to need to create.

We now create a new table with a row for each of the cards that we won with each hand. We use the trick discussed in Part 1 with the STRING_SPLIT and CROSS APPLY.

SELECT Card, CT.Value AS NewCard
INTO DetermineCards
FROM Day4Cards DC
CROSS APPLY STRING_SPLIT(CardsToMake, ' ') CT

From here, we are going to create several tables (or views) that show all of the cards created off of each card. We do this by the simple query shown below:

select DC1.*
into DetermineCards1
from DetermineCards DC
JOIN DetermineCards DC1 ON DC1.Card = DC.NewCard;

select DC1.*
into DetermineCards2
from DetermineCards1 DC
JOIN DetermineCards1 DC1 ON DC1.Card = DC.NewCard;

Notice that we’re truly doing a self-join on the table we created – only showing those cards that are created in the table we created. I only show two examples of the queries that will need to be written to go through all of the cards. You can continue this series (I hope you’re good at copy/paste) or you can write some fun dynamic SQL to create all of these tables.

Once that is complete, simply run the following query to find all of the rows in each of the tables we just created:

SELECT so.name, SUM(sddps.row_count)
FROM sys.objects so
JOIN sys.dm_db_partition_stats sddps on so.object_id = sddps.object_id
WHERE so.type = 'U'
	AND so.is_ms_shipped = 0x0
	and sddps.index_id < 2
	AND so.name like 'DetermineCards%'
group by so.name

For those of you who have never had the pleasure of working with this type of query – it will quickly find the row counts for each table in the so.name column. From here, you can again sum up all of these row counts into one massive number!

And that number, my friends, is the final answer for Part 2!

Lots of things going on with today’s code. Lots of loops. Some self-joins. And our old friends – STRING_SPLIT and CROSS APPLY!

With that, I hope you’re learning a lot of new functions in SQL and how to use them effectively. With that, it’s time to start on Day 5!