## Advent of Code 2017 Day 1–#SQLNewBlogger

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

I like the Advent of Code. I stumbled on the site a few years ago and enjoyed working through some of the challenges in 2015. I tried solving them with three languages (Python, PoSh, and T-SQL) but only managed to get through about half the items before life got busy. Last year I was worn out and too busy to mess with the code.

This year I was busy in December when the puzzles came out, but decided to work through the puzzles again as a break from work and life, and to keep my mind flexible. I’ll do a series here, hopefully all 25, but here’s my view of Day 1.

## Part 1

Each puzzle has two parts, and you need to solve part 1 to get part 2. In this case I need to take a list of input and compare each digit to the next digit and see if they match. If they do, then you add that digit to your sum.

This sounds like a perfect case to use the LEAD/LAG functions in SQL Server, so I did that. Since I get a long list of input, I also decided to use a string splitter. Here’s the first part where I split the string:

```DECLARE @i VARCHAR(5000);
--SET @i  = '1122';
--SET @i  = '951344679963668529';

WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
)
, cteSplit (i)
AS
( SELECT
i = SUBSTRING(@i, n, 1)
FROM myTally
WHERE n <= LEN(@i)
)

select i

from cteSplit```

If you run this, you’ll get your @i string out a character at a time. This get’s me the data in a set of rows, similar to an array.

Now I need to compare each row with the next one, and I’ll use the LEAD function. My data is ordered, so I don’t need an order. That means I’m looking at this:

`LEAD(i, 1) OVER (ORDER BY (SELECT NULL))`

I’ll compare that with the current value and if they match, I’ll return the value. If not, I return a zero, adding nothing to the sum.

There is one special case. The list is circular, so if the last digit (when we get there) matches the first digit, I need to include that. I’ll add that as special FIRST_VALUE, LAST_VALUE function match. Here’s my code:

```WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
)
, cteSplit (i)
AS
( SELECT
i = SUBSTRING(@i, n, 1)
FROM myTally
WHERE n <= LEN(@i)
)
, datacte(j)
AS
(
SELECT j = CASE WHEN i = LEAD(i, 1) OVER (ORDER BY (SELECT NULL))
THEN cteSplit.i
ELSE 0
END
FROM cteSplit
UNION all
SELECT TOP 1 j = CASE WHEN FIRST_VALUE(i) OVER (ORDER BY (SELECT NULL)) = LAST_VALUE(i) OVER (ORDER BY (SELECT NULL))
THEN i
ELSE 0
end
FROM cteSplit
)
SELECT SUM(j) FROM datacte```

When I do this with the input string, I get a result. It happened to be right ## Part II

Part II is a variation of Part I. Instead of looking at the next digit, I need to look halfway around the list. The list is an even number, so if it’s 10 digits long, I need to start with char 1 and look at char 6. If they are equal, add to sum. Then look at 2 and 7, and repeat. Since it’s a circular list, when I get to char 5, I look around to char 1. There’s an optimization here, but when I solved this, I decided to take an easy way out.

I first considered adding the first half of the list to the end and only running through the first len(input) characters. Then I thought, I could easily do a LAG. So, I modified my datacte query to do this:

```, datacte(j, k, i, l, n)
AS
(
SELECT j = CASE WHEN i = LEAD(i, @len) OVER (ORDER BY (SELECT NULL)) AND n <= @len
THEN cteSplit.i
ELSE 0
END
,
k = CASE WHEN i = LAG(i, @len) OVER (ORDER BY (SELECT NULL)) AND n > @len
THEN cteSplit.i
ELSE 0
END

)```

This quickly counts the matches up to the middle and then counts the next bunch.

There’s a better way, but I’ll leave you to figure that out in the comments.

In any case, I solved both of day 1. Now on to day 2 during the next break. ## About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

### 6 Responses to Advent of Code 2017 Day 1–#SQLNewBlogger

Comments are closed.