I enjoy when the Avent of Code comes around each year. I seem to make this a December (or sometimes New Year’s) resolution to get through them all, but life usually gets in the way. In any case, I decided to at least start this year and see how far I get.

Day 1 – First Puzzle

This is a simple one, and one that seems to lend itself to T-SQL. We have an input file that looks like:

+11

+9

-10

-5

etc.

This asks us to walk through the file, summing the values together and getting a new value. So the first row ends with 11. The next ends with 20 (11+9). The next is 10 (20-10), and so on. This feels like a simple calc, so let’s get it.

I wanted to load this with BULK LOAD, so I started with a table:

CREATE TABLE Day1(rawdata VARCHAR(20))

I know I’ll need to change this, but let’s make this easy. I use this command to now load my data.

BULK INSERT dbo.Day1 FROM 'C:\Users\way0u\Source\Repos\AdventofCode\2018\Day1\input.txt'

Once this is done, I’ll move on. Since I need to get this into some numeric values (this is a math problem), I’ll make another table.

CREATE TABLE Day1_a(frequency INT)

Now I move the data.

INSERT dbo.Day1_a

(
     frequency

)

SELECT CAST(rawdata AS int)

FROM dbo.Day1

GO

That seems to work fine. How do I get the end result? Well, addition doesn’t matter here, so I can do this:

SELECT SUM(frequency) FROM dbo.Day1_a

GO

I get an answer, plug it in, and viola, I’m right. That feels good.

Day 1 – Second Puzzle

This one is a little harder. I’m supposed to find out the first time that the end result repeats it’s value. The test cases show this working as follows:

Value    New result
0       0
1       1
-1      0

If I walk through this, the 0 repeats. The other test cases show this, but with the large input set, I need to change a few things.

1. I need to preserve ordering
2. I need to process this row by row.

The second item doesn’t mean that I’m looping necessarily, but I need to calculate out the sums as I go and potentially repeat the list.

To get started, let me modify my Bulk Insert and table to keep the ordering. I created this table.

CREATE TABLE Day1b(datakey INT IDENTITY(1,1), rawdata VARCHAR(20))

I then ran BULK INSERT. I got this error:

I tried a number of items, but nothing really worked. This was a very, very annoying error, and the main solution I saw on Stack Overflow was to add a column to the input file, which I don’t want to do. I initially thought this was a problem with the encoding, but it’s really the identity.

The best solution was a lower down answer, which was to create a view without the identity.

CREATE VIEW vDay1b

AS

SELECT rawdata
  FROM dbo.Day1b

GO

If I run the BULK INSERT to this view, it works fine.

OK. We’re moving and I have the data in order. Let’s move it to get the integer results we need.

CREATE TABLE Day1_2

( n INT, frequency INT)

GO

INSERT Day1_2
  SELECT datakey,
         CAST(rawdata AS INT)
   FROM dbo.Day1b

If I run a quick query that does a SUM() OVER(), I get a series of results. I can see there are no duplicates here.

OK, this means I need to repeat the data. I can re-insert data into the table, but that feels inefficient. I ought to be able to group data together.

Let’s do this by selecting the data as a group, but adding a value to it. I can do that with a cross join. Here’s a short example that illustrates this. Suppose I have a table with the values “Broncos”, “Chiefs”, “Raiders”, “Chargers”, I get select data like this in groups.

With that in mind, let’s create a tally table and start to duplicate data. I have no idea how many times, but having done the Advent of Code before, I’m guessing 5 groups isn’t enough. Let’s start with 100 repeats.

One note, I do need to start with 0, so we’ll use a UNION to add the 0 row. We don’t want the 0 row repeated, so we don’t add that to the table.