Advent of Code - Day 10
Tags: sql, beginners, programming, adventofcode
Published: 2022-12-11 04:39:18

Last week I've been to the [DevIntersection](https://www.devintersection.com/#!/) conference to present several sessions around Azure SQL and development ([Modern Architecture Patterns with Azure SQL Database]( https://www.devintersection.com/#!/session/Modern%20Architecture%20Patterns%20with%20Azure%20SQL%20Database/5712), [The 10 things every developer must absolutely know about Azure SQL](https://www.devintersection.com/#!/session/The%2010%20things%20every%20developer%20must%20absolutely%20know%20about%20Azure%20SQL/5454) and [Build a Jamstack solution in a day](https://www.devintersection.com/#!/workshop/Build%20a%20Jamstack%20solution%20in%20a%20day/5509)) so...yeah, I'm already falling behind with the challenges. Anyway. I'll try to catch up with the challenge I missed later.

I've also started using some of the [new or updated language elements introduced in SQL Server 2022](https://learn.microsoft.com/en-us/sql/sql-server/what-s-new-in-sql-server-2022?view=sql-server-ver16#language), also available in Azure SQL.

## Part 1

[Challenge 10](https://adventofcode.com/2022/day/10) can be solved using a non-equi join, so that each command provided as input will have exactly one line per cycle. Here's how I did it.

After importing the input using the usual `STRING_SPLIT` I have a table with one row per command:

![One row per command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/806ipyql6httcc9x49o1.png)

All commands operate only on the fictional variable `X`, which the challenge said start being set to `1`.

Using a running total, I can calculate cycle number at which each command is issued and what is the final value if `X` once the command has completed:

```sql
select
	*,
	sum(cycles) over (order by ordinal) as end_cycle,
	isnull(sum([value]) over (order by ordinal), 0) + 1 as end_value
from
	#commands
```

On that resultset, using the `LAG` operator, I can identify what is the value at the start and during the command execution, and what is the final value once the command is done. The challenge says, in fact, that the value of `X` is changed only once the command is finished, not at the beginning or during the operation.

```sql
select
	...
	lag(end_cycle, 1, 0) over (order by ordinal) as start_cycle,
	end_cycle,
	lag(end_value, 1, 1) over (order by ordinal) as start_value,
	end_value
from
	...
```

The result is a table with all the data needed to resolve the challenge.

![Added start and end cycle of command](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q41jnldsjiyt8dykyhsq.png)

Now I only have a _row per command_, but instead I need a _row per cycle_.

Not a big issue, since I have the start and end cycle of each command. I can generate a row per cycle by joining the one-row-per-command table with the usual numbers table (this time I'm using the new [`GENERATE_SERIES`](https://learn.microsoft.com/en-us/sql/t-sql/functions/generate-series-transact-sql?view=sql-server-ver16) introduced in SQL Server 2022 and available also on Azure SQL), I need to use a _non-equi join_ to generate as many rows as used cycles:

```sql
select
    ...
from
    #command_details cd
inner join
     generate_series(1, 10000) n on n.value-1 >= cd.start_cycle and n.value <= cd.end_cycle
```

Now I have one row per cycle:

![One row per cycle](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h0d7cq4zd2v3b58rckwx.png)

The next step is a simple aggregation, filtering by the requested cycles:

```
select 
    sum([cycle] * [start_value]) as [signal_strength]
from
    #cycles_exploded
where 
    cycle in (20, 60, 100, 140, 180, 220)
```

Part 1 done.

## Part 2

Part 2 is very interesting, as the goal is to "visualize" the result of a fictional low-res CRT display. The display only has 40 rows and 6 columns. The first thing I had to do was to convert to `cycle` value into horizontal and vertical coordinates. A division is enough to do the trick, and then I had to make sure that for each CRT line the leftmost position was set to 0, as explained in the challenge text:

```sql
select 
    *,
    (cycle-1)/40 as [line],
    row_number() over (partition by (cycle-1)/40 order by cycle) - 1 as crt_pos
from
    #cycles_exploded
order by
```

then the only thing left to do is to implement the logic to understand what character will be printed on the CRT, as described in the challenge:

```sql
iif(crt_pos between start_value - 1 and start_value + 1, '#', ' ') as [crt_char]
```

which will result in the following table:

![Rows with character to be displayed on the CRT](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5h85a697upm1qoj0bw4u.png)

and finally aggregate (making sure aggregation is done respecting the defined order, via the `WITHIN GROUP` supported by [`STRING_AGG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16)) the characters values into a string so that the solution can appear. Part 2 solved.

## Code

The solution as usual is on GitHub: https://github.com/yorek/aoc-2022/tree/main/day-10