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.

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);
8

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:

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/
17

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:

1mc = most common card, smc = second most common card
2
37. Five of a kind: mc=5
46. Four of a kind: mc=4
55. Full House: mc=3, smc=2
64. Three of a kind: mc=3
73. Two pair: mc=2, smc=2
82. One Pair: mc=2
91. High card mc=1
10

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.

1create or replace function day7_get_hand_rank (pi_hand in varchar2)
2  return number
3as
4  type t_char_map is table of number index by varchar2(1char);
5  l_char_map t_char_map;
6  /* map would look like this as JSON for hand 'A73A7':
7  {
8    "A": 2,
9    "7": 2,
10    "3": 1
11  }
12  */
13
14  l_char varchar2(1 char);
15  l_key  varchar2(1 char);
16  l_fir_highest number := 0;
17  l_sec_highest number := 0;
18begin
19  -- loop over eacht character and store it in the map
20  for i in 1..5
21  loop
22    l_char := substr(pi_hand, i, 1);
23    if l_char_map.exists(l_char) then
24      l_char_map(l_char) := l_char_map(l_char) + 1;
25    else
26      l_char_map(l_char) := 1;
27    end if;
28  end loop;
29
30  -- loop over the map to find the highest and second highest count
31  l_key := l_char_map.first;
32  while l_key is not null
33  loop
34    if l_char_map(l_key) > l_fir_highest then
35      l_sec_highest := l_fir_highest;
36      l_fir_highest := l_char_map(l_key);
37    elsif l_char_map(l_key) > l_sec_highest then
38      l_sec_highest := l_char_map(l_key);
39    end if;
40
41    l_key := l_char_map.next(l_key);
42  end loop;
43
44  dbms_output.put_line(
45    apex_string.format(
46      'l_fir_highest: %s, l_sec_highest: %s',
47      l_fir_highest,
48      l_sec_highest
49    )
50  );
51
52  -- ...
53

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.

1case l_fir_highest
2  when 5 then
3    l_res_str := '7';
4  when 4 then
5    l_res_str := '6';
6  when 3 then
7    if l_sec_highest = 2 then
8      l_res_str := '5';
9    else
10      l_res_str := '4';
11    end if;
12  when 2 then
13    if l_sec_highest = 2 then
14      l_res_str := '3';
15    else
16      l_res_str := '2';
17    end if;
18  when 1 then
19    l_res_str := '1';
20  else
21    raise no_data_found;
22end case;
23

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:

1create or replace function day7_get_hand_rank (pi_hand in varchar2)
2
3  -- ...
4
5  function get_card_value (pi_char in varchar2)
6    return varchar2
7  as
8  begin
9    case pi_char
10      when 'A' then
11        return '14';
12      when 'K' then
13        return '13';
14      when 'Q' then
15        return '12';
16      when 'J' then
17        return '11';
18      when 'T' then
19        return '10';
20      else
21        return '0' || pi_char;
22    end case;
23  end get_card_value;
24
25  -- ...
26

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:

1  for i in 1..5
2  loop
3    l_char := substr(pi_hand, i, 1);
4    if l_char_map.exists(l_char) then
5      l_char_map(l_char) := l_char_map(l_char) + 1;
6    else
7      l_char_map(l_char) := 1;
8    end if;
9
10    -- build the card value string
11    l_card_value_seq := l_card_value_seq || get_card_value(l_char);
12  end loop;
13

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.

1  l_res_str := l_res_str || l_card_value_seq;
2
3  dbms_output.put_line(
4    apex_string.format(
5      'l_res_str %s',
6      l_res_str
7    )
8  );
9
10  return to_number(l_res_str);
11end day7_get_hand_rank;
12

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:

1with hand_bet as (
2  select regexp_substr(line_str, '([0-9A-Z]+) [0-9]+', 1, 1, null, 1) as hand
3       , to_number(
4          regexp_substr(line_str, '[0-9A-Z]+ ([0-9]+)', 1, 1, null, 1)
5         ) as bet
6    from aoc_input
7   where day = 7
8     and key = 'SAMPLE'
9)
10select hand
11     , bet
12     , day7_get_hand_rank(hand) as score
13  from hand_bet
14 order by 3 desc
15;
16/*
17HAND        BET          SCORE
18________ ______ ______________
19QQQJA       483    41212121114
20T55J5       684    41005051105
21KK677        28    31313060707
22KTJJT       220    31310111110
2332T3K       765    20302100313
24*/
25

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:

1with hand_bet as (
2  select regexp_substr(line_str, '([0-9A-Z]+) [0-9]+', 1, 1, null, 1) as hand
3       , to_number(
4          regexp_substr(line_str, '[0-9A-Z]+ ([0-9]+)', 1, 1, null, 1)
5         ) as bet
6    from aoc_input
7   where day = 7
8     and key = 'INPUT'
9), scores as (
10  select hand
11       , bet
12       , day7_get_hand_rank(hand) as score
13       , row_number() over(order by day7_get_hand_rank(hand) asc) as rnk
14    from hand_bet
15   order by 4 desc
16)
17select sum(rnk * bet)
18from scores
19;
20
21/*
22   SUM(RNK*BET)
23_______________
24      255048101
25*/
26

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

1for i in 1..5
2loop
3  l_char := substr(pi_hand, i, 1);
4  if l_char_map.exists(l_char) then
5    l_char_map(l_char) := l_char_map(l_char) + 1;
6  elsif l_char = 'J' then
7    -- dont put into map
8    l_joker_count := l_joker_count + 1;
9  else
10    l_char_map(l_char) := 1;
11  end if;
12
13  l_card_value_seq := l_card_value_seq || get_card_value(l_char);
14end loop;
15

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

1if l_joker_count > 0 then
2  l_fir_highest := l_fir_highest + l_joker_count;
3end if;
4

Additionally, the value of the Joker card has changed. Instead of 11 we now return 01:

1case pi_char
2  -- ...
3  when 'J' then
4    return '01';
5

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.