Advent of Code 2018 – Day 4

As I explained in a recent post, I’m participating in this year’s Advent of Code challenge, with the twist of doing the challenges in T-SQL. In case you don’t know what “Advent of Code” is, Eric Wastl (t) created it for the purpose of, as Eric describes it:

Advent of Code is a series of small programming puzzles for a variety of skill levels. They are self-contained and are just as appropriate for an expert who wants to stay sharp as they are for a beginner who is just learning to code. Each puzzle calls upon different skills and has two parts that build on a theme.

The full explanation of “Advent of Code” is at //adventofcode.com/2018/about. You can see my other posts in the “Advent of Code” category.

2018 Advent of Code recap

We started of with someone going back in time and altering Santa’s past. You went back in time to correct it, but the device to fix the problems hadn’t been calibrated before sending you on your way. You calibrated the device on Day 1. On day 2, we located the boxes in the warehouse where the new (500 years ago) Santa suit material is. We sliced and diced the material on Day 3, finding the best piece to use.

Day 4, Part 1 – Loading the file

You need to sneak inside the Santa suit lab, but there is a guard outside it. Across the hall is a supply closet where you can observe things, and you see evidence of someone else observing the lab from there. Someone has written all over the walls their observation of the guard post. The observation covers only the midnight hour shift. It has the time (in YYYY-MM-DD hh:mm format), what guard went on shift, and when they fall asleep and wake up. The input file has an unsorted list of records. For this puzzle, the time recorded as “wakes up” means that the entire minute was awake. That minute is not counted as asleep.

For the first part of today’s puzzle, we need to figure out which guard spent the most time sleeping, and which minute of the hour was the guard asleep the most. The answer is the GuardID times the minute determined.

As we’ve done in the past, we start by loading the file and splitting it into rows.

Day 4 Input File

Day 4, Part 1 – Extracting data

A few things that we note about this file:

1. Only the record that marks the beginning of the shift has the guard number.
2. A guard may fall asleep more than once during the shift.
3. The guard can start the shift before or after midnight.
4. The time, guard ID#, and when the guard falls asleep and wakes up is extracted from the data.

All of these issues are handled during the processing of the file. To handle #3, I’m going to create an extra column with the date of the guard shift. This is the timestamp of the records, converting to just the date. We increment the date by one day to get the proper day of the shift when the hour is 23. The code for handling #3 and #4 is:

Load input file and extract data

Day 4, Part 1 – Determining the guard for the other records

Next, we need to get the guard ID # for the records that don’t have it. Since there is only one guard on watch at a time, we can compare the record with the guard ID to the ones without based on the shift date.

In cte2, cte is joined to itself based on the GuardDate. The second instance of cte is only looking at the records with the GuardID. This allows us to get the GuardID for all of the records.

Day 4, Part 1 – Calculating how long a guard was sleeping

Next, we need to calculate how long a guard was asleep. With the input file ordered by the time, we look at the rows when the guard wakes up, and then using the LAG function to look at the previous row (when the guard went to sleep). While we’re doing this, we will also get the minute the guard went to sleep, and the last minute the guard was asleep.

Guard’s sleep data

Day 4, Part 1 – Calculating the answer

It becomes a simple matter of adding up the MinutesAsleep for each guard to see which one was asleep the most:

However, we still need to get which minute that guard was most frequently asleep. Using a virtual tally table, we get all the minutes between the SleepStart and SleepEnd minutes from cte3. Join that to cte4 to only get the data for the sleepiest guard. I covered the virtual tally table in Day 2.

Day 4, Part 2 – Calculating the answer

With Part 2 unlocked, we have another scenario to check. Instead of getting the sleepiest minute for the sleepiest guard, this time we want to get which guard is most often asleep at the same minute. The calculation for the answer is the same: GuardID times the minute.

In looking over the requirements and the code we already have written, we see that we have all the parts that we need. The only thing that we need to change is to remove (or comment out) the line “JOIN cte4…”. Technically, we could remove the entire cte4 CTE, but the query will run just fine with it in place.

Summary

And here we have a T-SQL solution for Day 4 of the Advent of Code challenge. The key tasks that we can learn from today are:

• Loading a file.
• Split a string on a delimiter.
• Assigning a sequential number to a set of rows in a specific order.
• Construction of a virtual TALLY table.
• Using a TALLY table to extract each character from a string.
• Getting values from earlier rows.
• Use of the GROUP BY and HAVING clauses while performing an aggregation.