# Advent of Code 2023 in Oracle - Day 7: Camel Cards

## 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.

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:

## Day 7

#You can read the challenge text here.

### Star 1

#I noticed pretty quickly that ranking the games would be too complicated to achieve in pure SQL for my taste. So I opted to create a PL/SQL function that returns a numeric rank for a given hand. It will be numeric so that we can just sort by it to get the best/worst hands.

To achieve such a rank, we need to consider the highest factor. In the poker case, this is the “type” like “two pairs” or “four of a kind”. Notice how most types require only one piece of information and some two: **The count of the most common card and the count of the second most common card**. I just created the following scale:

To program this out, we first need to loop over each character to count the occurrences. We can use a map/associative array (`table of number index by varchar2(1char)`

) to store the counts. I have dedicated a whole blog post to maps in PL/SQL if you want to find out more. Afterward, we loop over our map to store the highest and second-highest counts.

Next, we can give scores to each type. I used a regular `case`

statement for this. For the cases where we need to consider the second-highest count, I added an `if`

statement. In the end, we add the rank number (higher → better) to a string.

The next question is: What happens if two hands have the same type? In this case, we need to compare the hands, card by card. On the first occurrence they differ we use the higher card to determine the winner. We can also achieve that with numerical sorting.

Because there are more than 10 cards, we have to pad the cards with a leading zero. This way, we can sort them numerically. We can use a function for this:

Inside the loop where we iterate over each character, we can use this function to get each card’s value. We just append every value to a string, so the first card value will be at the start of the string:

In the end, we can append the type result number to the front of the card value string so that it has the most impact on the number size. Now we only need to return the string converted to a number.

You can check out the full code of the function here.

To see how it works, we can query our input table and call the function. At first, I separate the hand and the bet for each line:

We can see that `QQQJA`

and `T55J5`

both start with a `4`

because both are three of a kind’s. The first has a higher card value. The first card is `Q`

vs a `T`

that’s why the second and third numbers are `12`

vs `10`

. Great; our function seems to work.

To get the result for our data input, we need to get a rank for each score and then multiply that with the bet (5 hands: best hand has rank 5, 2nd best has rank 4…). We can use the `row_number`

analytic function for this. At last, we need to multiply the rank by the bet and sum it up:

### Star 2

#For the second challenge, we must handle `J`

differently, as this is actually a Joker card. This means that this card should be used as any other card with the best potential outcome.

I thought this would be complicated, but it was actually pretty easy. We don’t need to write intelligent code that tries out different combinations, as the solution is to **always use the Joker as the most common card**. It never makes sense to go for two pairs or a full house, as a triplet or a four of a kind is always better. So `77J22`

should be treated as `77722`

and `JJJ77`

as `77777`

.

To achieve this, I started to count the Joker cards in the loop where we fill the map. Note that it is important that we don’t add the Jokers to the map itself. Otherwise, if the most common card is a joker, we would get the wrong result (applying jokers to jokers).

Before the `case`

statement that determines the type of the hand, we just add the number of jokers to the most common card count:

Additionally, the value of the Joker card has changed. Instead of `11`

we now return `01`

:

And that’s basically it. We now just use all the Jokers as the most common card. If we use the same query with the new function, we get the result.

## Conclusion

#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.