Advent of Code 2022 in Oracle - Day 10

My solution for the tenth day of the Advent of Code 2022 challenge "Cathode-Ray Tube" with Oracle. We simulate a CPU and a Pixel-Screen in pure SQL!

Advent of Code

Advent of Code is a cool yearly programming challenge. Each day from 1st to 25th December a new challenge appears. You can solve it in any programming language you like. After deepening my Go skills last year I will do this in Oracle (so PL/SQL and SQL) this year.

I try to blog about my solutions but I won’t make it every day or give up after a while as they get more difficult and time-consuming. You can find my solutions for this year in this repository.

I really suggest you check Advent of Code out yourself as it is a lot of fun and you will improve your problem-solving skills.

The challenges are clearly phrased with a simple example you can do in your head. You then get a way bigger generated and user-specific input and have to solve the challenge with that. You have to enter a solution in an input field and get a star for each day you solved. There is also a second challenge every day where you have to adapt some part of your algorithm to solve it.

Challenge 1

You can find the challenge here.

Today’s challenge was really fun. We receive some inputs that look like this:

These are inputs for a CPU we have to simulate. Noop does nothing for one cycle whereas addx takes two cycles. In the first cycle nothing happens in the second we add up the given value to a register “x”.

In the end, we have to calculate the state of the register at the cycles 20, 60, 100, 140, and 180. We also have to multiply the state by the cycle number to get the signal strength and sum up all these values.

Solving challenge 1

Parsing the input

After parsing cryptic input for the last 10 days this was a breeze. We split the lines and extract the command (noop or addx) and the value:

Simulating the CPU Cycles

We can then simulate the CPU cycles by creating a second row for every addx command as it takes two cycles. I added a cycle_no column with row_number so we always know in which cycle we are. I also only display the add_x value for the second cycle of an addx command as it gets added after the first cycle and we can sum the column up now without getting adding the values two times. Additionally, I added an initial state with x = 1:

Now we have a table with all the commands and their cycles. The next step is to calculate the state of the register at the given cycle. For that, we can use rolling sums with the sum window function. The function adds up all the values in the add_x column until the current row. Additionally, we can calculate the signal strength by multiplying the state with the cycle number:

Getting the result

Now we can just filter for the cycles we need and sum up the signal strength:

You can check out the full solution here.

Challenge 2

The second part goes a few steps further. Here I struggled a lot because of one little misunderstanding of the challenge description that lead to a completely different solution. Lesson learned for me: Read the challenge description carefully!

Turns out the CPU also renders pixels onto a screen with a width of 40 pixels. The x-register value is used for a sprite that is 3 pixels wide (so +1 and -1 of x) and 1 pixel high.

If the current pixel position (basically CPU Cycle) is within the sprite range we draw a pixel. Because the screen is also 6 pixels high, cycles 1 - 40 are for the first row, 41 - 80 for the second, and so on.

If we draw the screen correctly we can see letters appearing that we have to input to complete the challenge.

Solving challenge 2

We start with our prior query and move on by getting the current state of the register at every cycle. Then we need to figure out at which cycle which pixel is drawn:

With that we can easily determine if a pixel should be drawn or not by checking if the pixel position is within the sprite range:

Now comes the tricky part, how to draw the screen?! I eventually went with a huge pivot query and some SQLcl column format options to get the result drawn in a readable way:

Seeing the resulting letters take shape was a beautiful moment. I took some time as I thought that at the render time the sprite was not yet updated. This small change caused the output to look really chaotic. I thought I was doing something fundamentally wrong. So close and yet so far… But I eventually figured it out!

You can check out the full solution here.


loading comments...