Advent of Code#
Advent of Code is a great yearly programming challenge. Each day (1st to 25th December), a new challenge arises that challenges your problem-solving and programming skills. I learn a lot every year, and it is a lot of fun.
I will do the challenges in Oracle (SQL and, where necessary, PL/SQL) this year again. You can find my solutions in this repository and, if I find the time, on this blog.
I strongly recommend checking out Advent of Code yourself before reading on. The challenges are clearly and in a Christmas theme. There is always a small sample input to test your solution and a bigger input to get the real result. When you solve the first challenge, you get a star, and a second, more difficult challenge appears.
I am doing the challenges on a local 23c free edition database. I am eager to explore some of the new features of 23c, so some of the syntax might not work on older versions. Furthermore, I will also use the Oracle APEX API packages.
I import the input data into a table that stores each line of the input as a row.
I then use SQL Developer to import the input data into the table. I use a bind for the input and paste the data from the website:
We can start by removing all non-numeric characters from the lines:
Next, we need to extract the first and last digits of each line. We can just use
substr for that. With
1 we get the first, and with
-1 the last character. Additionally, we need to concatenate both characters and convert the result to a number:
At last, we only have to sum up all the results:
I really struggled with this one. We have to convert the words of digits like
one to the actual number
1. At first, I thought a simple
replace would do the trick. But this does not work, as
eighthree should actually be converted to
83. With the replace approach, we would replace the shared
t and thus the second replace would not work (e.g.
I was already at the end of my SQL skills and thought only PL/SQL could solve this. So I created a function that loops over each character of the input and looks to see if there is a digit. If this is true, I append the digit to a result string; otherwise, I append the character itself. This works, but is not very elegant.
We can now apply the same logic as in star 1 to get our result:
This challenge is a great example of how great SQL is for data analysis. I noticed pretty quickly that we want to get the highest count for each colored block of each line. So we will probably need the
Additionally, we would like to get the counts for each color. Repeated text blocks scream regex to me. To catch each amount of green blocks in
Game 1: 3 blue, 4 red; 1 red, 2 green, 6 blue; 20 green e.g. a regex like
([0-9]+) green will work. Notice how I use the brackets to capture the number.
Another trick to do this in a quick fashion is using
apex_string.grep. This function returns the beautiful
APEX_T_VARCHAR2 type to which I dedicated a whole blog post.
We can use this and the regex to capture the numbers:
More interestingly, we can use the
table operator to query the array. With that, we can just ask for the maximum value. The
table thing returns the column
column_value which we first need to convert to a number.
We can apply that pattern to every color to get the max count for each color:
Next, we can just apply a where clause to filter out all the impossible games and sum the
line_no to get our result:
Star 2 was no problem at all because of our work on Star 1. We only need to multiply the maximum values for each color and sum the results:
If you find different or better solutions, please let me know in the comments. I am always eager to learn new things.
You can check out the full code on GitHub.