Advent of Code 2022 in Oracle - Day 5

My solution for the fifth day of the Advent of Code 2022 challenge "Supply Stacks" with Oracle. Its starting to get complex...

Solution for Day 4

As I enjoyed my weekend yesterday I did not describe my solution for day 4. I still solved it you can find my solution on GitHub. Spoiler: it was fairly easy with SQL. For today’s challenge, I used a fairly complex approach (I struggled a lot…).

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 find the challenge here.

The elves have a crane and different stacks of goods. We get the initial stack setup in the first part of the input. Below that we get a list of commands to move the goods around. We need to find the topmost good in every stack after all commands are executed.

This is the sample input:

Solving challenge 1

Parsing the initial stack setup

We first need to parse the initial stack setup. To make it easier to parse I want to change the not filled goods from spaces to [-]. Basically, I want it to look like this:

This was more complex than I thought. As an empty box is represented as three spaces I replaced four spaces with [-]. This works great except for an empty box in the first stack:

So I needed to add another replace that adds a space between two boxes that are not separated by a space. An additional trim removes the leading space. I then excluded all not stack lines with a simple like resulting in the following query:

My next step is to get each box in a single line. In the real input, the amount of stacks is 8 instead of 3 so I need this query to work for any amount of stacks. To achieve that I generated the rows with a hierarchical query and got each box with a regexp_substr:

I got the hierarchical query approach from Kim Berg Hansen’s book Practical Oracle SQL. I highly recommend it if you want to deepen your SQL skills.

Parsing the crane inputs

Getting the crane inputs into a relational structure is fairly easy. We need to split the input by lines, only get the ones that start with move, and then extract the relevant information with regexp_substr:

Store stack state

To simulate the crane inputs we need to keep track of the current state of the stacks. For that I created a PL/SQL map / associative array I can access via the stack number. Each entity holds another array of the blocks in the stack indexed by the block number (1 lowest stacking up to X).

To set up the initial state I used a bulk collect query for each stack:

Simulating the crane inputs

To process the crane inputs I looped over every entry of the stored moves. In there I need another loop to process multiple blocks being transferred from the same stack one after another. The moving part itself is just getting the last block of the “from” stack and adding it to the “to” stack. And don’t forget to delete the removed block from the “from” stack:

Output sequence

After all moves are processed we can get the final sequence by logging the top block of every stack:

You can check out the full solution here.

Challenge 2

Part 2 just needs a small change to process the crane inputs. In the first part, the crane moved one box at a time meaning that when it moves more than one block the order gets reversed during this process.

In the second part the crane instead grabs multiple blocks at once and moves them in the same order they were stacked.

Solving challenge 2

To achieve that I first collect all blocks from a single move operation in a temporary array and then move them to the new stack in the same order with a reversed loop:

You can check out the full solution here.

I really enjoyed this challenge. I can imagine that there are many different approaches to solving it. I would love to see your solutions.


loading comments...