2021 Advent of Code–#SQLNewBlogger

2021 Advent of Code–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ll do a post on how to easily get started here as a new blogger, but following the Advent of Code, even some random problems, is a good way to show off some T-SQL skills.

This won’t be a goal for me, but I did start working on the 2021 Advent of Code, taking a few minutes across some days to break from other work and solve a programming problem.

My aim this time is not to get stuck on a problem. If I can’t solve it, or don’t see a way, I leave it and move on. This post has a few thoughts on the first few days.

The first problem dealt with loading a set of data and then counting how many times the number increases from the previous number.

Since numbers in a SQL table don’t have a guaranteed order, this was a bit of a hack from me. I created a table and added a clustered index, and then bulk inserted the data. I then moved this in the same order (I hope) to a table with an identity column. From here, simple LEAD to find the differences between consecutive rows and counting these.

The second part changed the calculation slightly to use groups of three rows. I copied my LEAD formula to include 3 rows instead of just 1 in each side of the calculation.

We’re in a submarine, moving forward or up/down. The input was something I needed to evaluate in order again, so I repeated a similar load. Then I used SUBSTRING and CASE to decide what type of instruction was needed and sum the results.

Part 2 was tricky. I bailed initially, as I couldn’t quite get the math down in my head. I eventually set up a small test data set using the values on the site and then used that to calculate things. I had a series of CTEs that I used to extract the values, then get changes, then perform the math.

Letting part 2 sit for a day in my head helped me focus better.

Day 3 was fairly easy binary counting. The test data doesn’t depend on order, so I just loaded it up. Then I need to extract the values into the bits, so SUBSTRING each of these out in a CTE. Not dynamic, but it was easy to extract all 12 bits, then count up the number of 1s and 0s, deciding which was more prevalent.

From there, a simple calc to assemble back the counts into a binary number and convert to decimal.

Part 2 is really about counting the 1s and 0s in each position, then creating a final binary number from this and converting back to decimal. I had to read carefully here, as you need to reduce your input set each time. I ended up looping here, as I couldn’t find an easy way to do this otherwise. I could have added some flag to ignore rows, but ended up with a temp table and deletes to get this done.

So far, easy, harder, then easier.