Advent of Code 2022 in Oracle - Day 1

My solution for the first day of Advent of Code 2022 challenge "Calorie Counting" with Oracle.

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.

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

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.

Solving challenge 1

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

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:

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

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

Challenge 2

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

Solving challenge 2

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:

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!


loading comments...