Last week I've been to the DevIntersection conference to present several sessions around Azure SQL and development (Modern Architecture Patterns with Azure SQL Database, The 10 things every developer must absolutely know about Azure SQL and Build a Jamstack solution in a day) 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, also available in Azure SQL.

Part 1

Challenge 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:

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:

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.

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.

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 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:

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:

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:

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:

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

which will result in the following table:

and finally aggregate (making sure aggregation is done respecting the defined order, via the WITHIN GROUP supported by STRING_AGG) 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