Advent of Code 2023 in Oracle - Day 1 and 2
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.
My setup
#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.
1create table aoc_input (
2 day integer not null -- 1-25
3, key varchar2(255 char) not null -- 'SAMPLE1', 'INPUT' etc.
4, line_no integer not null -- 1, 2, 3, ...
5, line_str varchar2(4000 char) not null -- The actual line
6, constraint aoc_input_pk primary key (day, key, line_no)
7);
8I 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:
1begin
2 insert into aoc_input
3 (day, key, line_no, line_str)
4 with x as (
5 select :bind as data from dual
6 )
7 select 1
8 , 'INPUT' -- or something like 'SAMPLE1'
9 , rownum
10 , column_value
11 from x
12 , apex_string.split(x.data, chr(10));
13
14 commit;
15end;
16/
17Day 1
#You can read the challenge text here.
Star 1
#We can start by removing all non-numeric characters from the lines:
1select line_no
2 -- remove all non numeric characters
3 , regexp_replace(line_str, '[^0-9]', '') as line
4 from aoc_input
5 where day = 1
6 and key = '1'
7 order by line_no
8;
9/*
10 LINE_NO LINE
11__________ _______
12 1 9
13 2 1
14 3 37
15 4 38
16 5 38
17*/
18Next, 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:
1with only_nums as (
2 select line_no
3 -- remove all non numeric characters
4 , regexp_replace(line_str, '[^0-9]', '') as line
5 from aoc_input
6 where day = 1
7 and key = '1'
8 order by line_no
9)
10select line_no
11 -- concat first and last character, convert res to number
12 , to_number(substr(line, 1, 1) || substr(line, -1, 1)) as row_res
13 from only_nums
14;
15/*
16 LINE_NO ROW_RES
17__________ __________
18 1 99
19 2 11
20 3 37
21 4 38
22 5 38
23*/
24At last, we only have to sum up all the results:
1with only_nums as (
2 select line_no
3 -- remove all non numeric characters
4 , regexp_replace(line_str, '[^0-9]', '') as line
5 from aoc_input
6 where day = 1
7 and key = '1'
8 order by line_no
9), row_res as (
10 select line_no
11 -- concat first and last character, convert res to number
12 , to_number(substr(line, 1, 1) || substr(line, -1, 1)) as row_res
13 from only_nums
14)
15-- sum up all row results
16select sum(row_res) from row_res
17;
18Star 2
#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. 8hree).
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.
1CREATE OR REPLACE FUNCTION day1_replace_digits(pi_str VARCHAR2) RETURN VARCHAR2 IS
2 l_temp_str VARCHAR2(4000);
3 l_replaced_str VARCHAR2(4000) := '';
4BEGIN
5 dbms_output.put_line(pi_str);
6 for i in 1 .. length(pi_str)
7 loop
8 l_temp_str := substr(pi_str, i);
9
10 case
11 when l_temp_str like 'one%' then
12 l_replaced_str := l_replaced_str || '1';
13 when l_temp_str like 'two%' then
14 l_replaced_str := l_replaced_str || '2';
15 when l_temp_str like 'three%' then
16 l_replaced_str := l_replaced_str || '3';
17 when l_temp_str like 'four%' then
18 l_replaced_str := l_replaced_str || '4';
19 when l_temp_str like 'five%' then
20 l_replaced_str := l_replaced_str || '5';
21 when l_temp_str like 'six%' then
22 l_replaced_str := l_replaced_str || '6';
23 when l_temp_str like 'seven%' then
24 l_replaced_str := l_replaced_str || '7';
25 when l_temp_str like 'eight%' then
26 l_replaced_str := l_replaced_str || '8';
27 when l_temp_str like 'nine%' then
28 l_replaced_str := l_replaced_str || '9';
29 else
30 l_replaced_str := l_replaced_str || substr(pi_str, i, 1);
31 end case;
32
33 end loop;
34
35 dbms_output.put_line(l_replaced_str);
36 RETURN l_replaced_str;
37END day1_replace_digits;
38/
39
40
41-- Example
42-- Input: four5djlmjfive99eightonefour
43-- Output: 4our5djlmj5ive998ight1ne4our
44We can now apply the same logic as in star 1 to get our result:
1with digit_str as (
2 select line_no
3 , day1_replace_digits(line_str) as line_str
4 , line_str as orig
5 from aoc_input
6 where day = 1
7 and key = '1'
8), only_nums as (
9 select line_no
10 --remove all non-digits
11 , regexp_replace(line_str, '[^0-9]', '') as line
12 , line_str
13 , orig
14 from digit_str
15 order by line_no
16), row_res as (
17 select line_no
18 , to_number(substr(line, 1, 1) || substr(line, -1, 1)) as row_res
19 , line_str
20 , orig
21 from only_nums
22)
23select sum(row_res) from row_res
24;
25Day 2
#You can read the challenge text here.
Star 1
#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 max function.
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:
1with str as (
2 select 'Game 1: 3 blue, 4 red; 1 red, 2 green, 6 blue; 20 green' as s
3)
4select apex_string.grep(s, '([0-9]+) green', 'i', '1') as arr
5 from str
6;
7/*
8ARR
9__________
10[2, 20]
11*/
12More 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.
1with str as (
2 select 'Game 1: 3 blue, 4 red; 1 red, 2 green, 6 blue; 20 green' as s
3)
4select (select max(to_number(column_value)) from table(apex_string.grep(s, '([0-9]+) green', 'i', '1') ) ) as max
5 from str
6;
7/*
8 MAX
9______
10 20
11*/
12We can apply that pattern to every color to get the max count for each color:
1select line_no
2 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) green', 'i', '1') ) ) as green
3 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) blue', 'i', '1') ) ) as blue
4 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) red', 'i', '1') ) ) as red
5 , line_str
6 from aoc_input
7 where day = 2
8 and key = 'INPUT'
9 order by line_no
10;
11/*
12 LINE_NO GREEN BLUE RED LINE
13__________ ________ _______ ______ ____________________________________
14 1 2 15 12 Game 1: 1 green, 2 blue; 15 bl...
15 2 9 19 6 Game 2: 5 green, 2 red, 18 blu...
16 3 10 8 16 Game 3: 16 red, 10 green; 12 r...
17 4 20 9 1 Game 4: 9 blue, 20 green; 1 re...
18 5 4 1 10 Game 5: 3 green, 8 red; 1 blue...
19*/
20Next, we can just apply a where clause to filter out all the impossible games and sum the line_no to get our result:
1with max_draws as (
2 select line_no
3 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) green', 'i', '1') ) ) as green
4 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) blue', 'i', '1') ) ) as blue
5 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) red', 'i', '1') ) ) as red
6 , line_str
7 --, regexp_substr(line_str, '[0-9]+ green')
8 from aoc_input
9 where day = 2
10 and key = 'INPUT'
11 order by line_no
12), possible_games as (
13 select *
14 from max_draws
15 where red <= 12
16 and green <= 13
17 and blue <= 14
18)
19select sum(line_no)
20from possible_games
21;
22Star 2
#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:
1with max_draws as (
2 select line_no
3 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) green', 'i', '1') ) ) as green
4 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) blue', 'i', '1') ) ) as blue
5 , (select max(to_number(column_value)) from table(apex_string.grep(line_str, '([0-9]+) red', 'i', '1') ) ) as red
6 , line_str
7 from aoc_input
8 where day = 2
9 and key = 'INPUT'
10 order by line_no
11), power as (
12 select max_draws.*, green * blue * red as pwr
13 from max_draws
14)
15select sum(pwr) from power
16;
17Conclusion
#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.