Advent of Code 2022 in Oracle - Day 1

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.

You can read the challenge yourself here.

We get a user input that consists of numbers and line breaks:

11000
22000
33000
4
54000
6
75000
86000
9
107000
118000
129000
13
1410000
15

The numbers represent the calories of a single food item an elf has. A completely empty line states that the data for the next elf starts. So the first elf has 1000, 2000, and 3000 calories, and the second elf has 4000 calories.

We must find out how many calories the elf with the most calories has.

We need to store the data. So let’s first create a table:

1CREATE TABLE elf_calories (
2  id           NUMBER,
3  calories     NUMBER,
4  constraint elf_pk primary key (id)
5)
6INMEMORY;
7

The input I received has over 2000 lines. So I must find a way to programmatically get the data into the table.

First off I wanted to group the data by single elves and then add up the single calories. So I first split the line by two line breaks and then split the resulting lines by a single line break. For that I use apex_string.split to get an apex_t_varchar2 array.

Afterward, we can just sum up the calories for each elf and then store the result in the table:

1declare
2  l_input varchar2(32767 char) := q'!6471
31935
41793
5...
6...
7...
816077
9
106030
111807!';
12
13  l_elves        apex_t_varchar2;
14  l_elf_str      varchar2(2000 char);
15  l_elf_calories apex_t_varchar2;
16
17  l_cal_sum      pls_integer;
18  l_single_cal   pls_integer;
19begin
20  -- split input by two LFs -> array for every elf
21  l_elves := apex_string.split(l_input, apex_application.LF || apex_application.LF );
22  dbms_output.put_line('Elf count: ' || l_elves.count);
23
24  for i in 1..l_elves.count
25  loop
26    l_cal_sum := 0;
27    l_elf_str := l_elves(i);
28    l_elf_calories := apex_string.split(l_elf_str, apex_application.LF);
29
30    for j in 1..l_elf_calories.count
31    loop
32      -- convert single cal string to number
33      l_single_cal := to_number(l_elf_calories(j));
34      l_cal_sum := l_cal_sum + l_single_cal;
35    end loop;
36
37    -- insert sum of calories into table
38    insert into elf_calories values(i, l_cal_sum);
39  end loop;
40
41end;
42/
43

To get the result we just need to query the data for the maximum calories:

1prompt Most calories:
2select *
3  from elf_calories
4 where calories = (select max(calories) from elf_calories)
5;
6
7

Et voilà, we get our result. Check out the full solution here.

For challenge two we have to get the sum of calories for the 3 elves that have the most calories.

This part is super easy when we have access to SQL so we just need to add a small query. With the rank analytic function we can get a sequential order (ranking) for a custom order or even with groups. We just rank the calories column in descending order:

1prompt Top Three elves sum:
2with rank_data as (
3select id, calories, rank() over (order by calories desc)  as cal_rank
4  from elf_calories
5)
6 select sum(calories)
7   from rank_data
8  where cal_rank between 1 and 3
9;
10

Check out the full solution here.

This was a fairly easy challenge so let’s see what the next days will bring.

If you have a different approach or have some optimizations for my solution, please let me know!