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.
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
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:
sum(cycles) over (order by ordinal) as end_cycle,
isnull(sum([value]) over (order by ordinal), 0) + 1 as end_value
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.
lag(end_cycle, 1, 0) over (order by ordinal) as start_cycle,
lag(end_value, 1, 1) over (order by ordinal) as start_value,
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:
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 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:
(cycle-1)/40 as [line],
row_number() over (partition by (cycle-1)/40 order by cycle) - 1 as crt_pos
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.
The solution as usual is on GitHub: https://github.com/yorek/aoc-2022/tree/main/day-10