Advent of Code 2022 in Oracle - Day 2
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:
1A Y
2B X
3C Z
4The 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:
1create table rps_options (
2 shape varchar2(1)
3, wins_against varchar2(1)
4, loses_against varchar2(1)
5, points number
6) inmemory;
7
8-- R -> Rock, P -> Paper, S -> Scissors
9insert into rps_options values ('R', 'S', 'P', 1);
10insert into rps_options values ('P', 'R', 'S', 2);
11insert into rps_options values ('S', 'P', 'R', 3);
12The second table holds the plays:
1create table rps_plays (
2 id number
3, shape_opo varchar2(1)
4, shape_own varchar2(1)
5, encrypted_own varchar2(1)
6, constraint pk_rps_plays primary key (id)
7) inmemory;
8To get prepare the plays data I inserted the input into a single table with a clob col:
1create table rps_input (
2 input clob
3) inmemory;
4We 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:
1insert into rps_plays
2 with plays_concat as (
3 select rownum id
4 , column_value play
5 -- split by linefeed with apex_string.split
6 from table( select apex_string.split(input) from rps_input )
7 )
8 select id
9 -- \S matches a non-whitespace character
10 -- translate instead of three replaces: All A -> R, B -> P, C -> S
11 , translate(regexp_substr(play, '\S', 1, 1), 'ABC', 'RPS') as shape_opo
12 , translate(regexp_substr(play, '\S', 1, 2), 'XYZ', 'RPS') as shape_own
13 , regexp_substr(play, '\S', 1, 2) as encrypted_own
14 from plays_concat
15;
16With 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:
1prompt First 10 play results
2select id
3 , shape_opo
4 , shape_own
5 -- wins_against has the sign our sign wins against
6 -- 6 pts for win, 3 for draw, 0 for lose
7 , case when shape_opo = wins_against then 6
8 when shape_opo = loses_against then 0
9 else 3
10 end
11 + points
12 as result
13 from rps_plays p
14 join rps_options o
15 on p.shape_own = o.shape
16 order by id
17 fetch first 10 rows only;
18Resulting in:
1 ID SHAPE_OPO SHAPE_OWN RESULT
2_____ ____________ ____________ _________
3 1 S R 7
4 2 S R 7
5 3 R P 8
6 4 S R 7
7 5 P P 5
8 6 R R 4
9 7 R S 3
10 8 P P 5
11 9 S S 6
12 10 S S 6
13To get the output score we just need to sum the points up:
1prompt Score
2with data as (
3select id
4 , shape_opo
5 , shape_own
6 , case when shape_opo = wins_against then 6
7 when shape_opo = loses_against then 0
8 else 3
9 end
10 + points
11 as result
12 from rps_plays p
13 join rps_options o
14 on p.shape_own = o.shape
15)
16select sum(result) as score
17 from data
18;
19Great, 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:
1prompt First 10 play results
2with data as (
3select id
4 , shape_opo
5 , shape_own
6 -- we want to Z -> win, Y -> draw, X -> lose
7 , case when encrypted_own = 'Z' then 6
8 when encrypted_own = 'X' then 0
9 else 3
10 end
11 as result_points
12 -- when we want to lose we play 'loses_against' of the opponent's sign
13 , case when encrypted_own = 'Z' then loses_against
14 when encrypted_own = 'X' then wins_against
15 else shape_opo
16 end
17 as what_i_need_to_play
18 , encrypted_own
19 from rps_plays p
20 join rps_options o
21 on p.shape_opo = o.shape
22)
23 select id
24 , shape_opo
25 , encrypted_own
26 , what_i_need_to_play
27 , o.points + result_points as points
28 from data d
29 join rps_options o
30 on d.what_i_need_to_play = o.shape
31 order by id
32 fetch first 10 rows only;
33Resulting in:
1 ID SHAPE_OPO ENCRYPTED_OWN WHAT_I_NEED_TO_PLAY POINTS
2_____ ____________ _______________ ______________________ _______
3 1 S X P 2
4 2 S X P 2
5 3 R Y R 4
6 4 S X P 2
7 5 P Y P 5
8 6 R X S 3
9 7 R Z P 8
10 8 P Y P 5
11 9 S Z R 7
12 10 S Z R 7
13We 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!