Advent of Code 2022 in Oracle - Day 2

My solution for the second day of the Advent of Code 2022 challenge "Rock Paper Scissors" with Oracle. This time 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 read the challenge yourself here.

Today is about Rock Paper Scissors.

We get an encrypted input that consists of A, B, or C a whitespace X, Y, Z, and then a linefeed:

The first letter stands for what the opponent plays and the second is what we should play. A or X stands for rock, B or Y for paper, etc…

We need to calculate and summarize points for every game. We get 0 for a loss, 3 for a draw, and 6 for a win. Additionally, we get points for what we play. For some rather funny reason, the signs are not equally treated. Rock is worth 1 point, paper 2, and scissors 3.

The task is understood so let’s code…

Solving challenge 1

This time I want to do it in pure SQL.

The first table we create is a rule table. For every possible sign we insert against which we win and lose and the points it is worth:

The second table holds the plays:

To get prepare the plays data I inserted the input into a single table with a clob col:

We can then go ahead and split the input first by lines and second by whitespace to get each player’s sign. I also used the translate function to decrypt both player’s signs to represent the actual sign:

With this set up we can go ahead and calculate the points for each play. We can do this by joining the plays with the options table on our sign and checking whether the opponent’s sign is equal to wins_against or loses_against or else the game is a draw.

We assign the defined points to the outcome and also add up the points column of our played sign:

Resulting in:

To get the output score we just need to sum the points up:

Great, we are done! Check out the full solution here.

Challenge 2

So it turns out we understood the X, Y, Z column wrong. It is not our encrypted sign but the outcome we want to achieve. So X stands for we want to lose, Y for a draw, and Z for a win.

We still need to calculate the points for the game but now need to find out what we should play to achieve the desired outcome.

Solving challenge 2

We just need to modify the query a bit by first adding a new subquery. First I assign the points for our desired outcome and store them in the col result_points.

Then I join the options table again but this time on the opponent’s play. Now I can find out what we should play by either selecting wins_against, loses_against, or the opponent’s play.

I then query against that subquery and join the options table again to get the points for our play. Then I just add these points up with our outcome points and we get our results:

Resulting in:

We can again sum up the points and also solved the second part! Check out the full solution here.

Let me know if you used a different approach or have some feedback!


loading comments...