How to Solve Advent of Code 2022 Using Postgres...

Spoiler Alert!

This article will contain spoilers both on how I solved 2022 Day 2's challenge "Rock Paper Scissors" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.

Hands on Tutorial

We've also loaded a tutorial for Day's 2 challenge if you want to try it with a pre-loaded data set.

AOC Day 2

Things get a little harder on Day 2 of the Advent of Code. The puzzle, based on the classic "Rock Paper Scissors" game, is a text file with two bits of information per line. Things we are using to solve this days challenge include:

file_fdw, a built-in foreign data wrapper for Postgres
The CASE command
A plpgsql function to help us play rock paper scissors
A custom operator that calls the function
The ascii() function, to map characters to a number

For this puzzle, we are given the opposing player's entry in a Rock Paper Scissors (RPS) game, where the letters A B C map to "rock paper scissors". We are also given "our" entry, mapping X Y Z to the same three values. Each round, or line in the file, gets two scores assigned to it. The shape score depends on what you play, where rock is 1, paper is 2, and scissors is 3. The outcome score is set based on how well the round went for you. A win is 6 points, a draw is 3, and a loss is 0. What we want is the grand total of all scores across all rounds (2500 of them in this case).

As before, we will use the foreign data wrapper file_fdw to slurp in the file we have saved to disk as /tmp/aoc2022.day2.input. This time we have two single-letter characters separated by a space, so we need to provide that space as a delimiter when defining the foreign table. We will use the handy IF NOT EXISTS clause to ensure that the things we already did for Day 1 will not cause errors if we try to install them again.

CREATE EXTENSION if not exists file_fdw;

CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw;

DROP FOREIGN TABLE if exists aoc_day2;

CREATE FOREIGN TABLE aoc_day2 (you char, me char)
  SERVER aoc2022 options(filename '/tmp/aoc2022.day2.input', delimiter ' ');

Our test file looks like this:

C Z
B Y
C X
B Z
C Y
B Y
C Z
C Z
B Y
B X

Note: I'm just using these 10 rows, if you use the full file you'll get different SUMs at the end.

Let's take a quick peek at the table as read in by Postgres, and make sure it gets processed as we expect:

SELECT * FROM aoc_day2;

 you | me
-----+----
 C   | Z
 B   | Y
 C   | X
 B   | Z
 C   | Y
 B   | Y
 C   | Z
 C   | Z
 B   | Y
 B   | X
(10 rows)

Looks fine. While there are quicker ways to solve this, let's be more verbose in the spirit of the contest. We will convert those characters to what they represent in the RPS game:

SELECT *,
  CASE WHEN you='A' THEN 'Rock' WHEN you='B' THEN 'Paper' ELSE 'Scissors' END AS yourplay,
  CASE WHEN me ='X' THEN 'Rock' WHEN me ='Y' THEN 'Paper' ELSE 'Scissors' END AS myplay
FROM aoc_day2;

 you | me | yourplay |  myplay
-----+----+----------+----------
 C   | Z  | Scissors | Scissors
 B   | Y  | Paper    | Paper
 C   | X  | Scissors | Rock
 B   | Z  | Paper    | Scissors
 C   | Y  | Scissors | Paper
 B   | Y  | Paper    | Paper
 C   | Z  | Scissors | Scissors
 C   | Z  | Scissors | Scissors
 B   | Y  | Paper    | Paper
 B   | X  | Paper    | Rock

Much better. But now we have to determine which side wins - in other words, that Rock beats Scissors, but Paper beats Rock. That's a lot of IF/ELSE work, so let's put that inside a plpgsql function that takes two inputs and returns the result:

CREATE OR REPLACE FUNCTION rps(me text, you text)
  returns text language plpgsql IMMUTABLE as $$
BEGIN
 me = lower(me); you = lower(you);
 RAISE DEBUG 'Result of % vs %:', me, you;
 IF me = you                             THEN RETURN 'DRAW'; END IF;
 IF me = 'rock'     AND you = 'scissors' THEN RETURN 'WIN';  END IF;
 IF me = 'rock'     AND you = 'paper'    THEN RETURN 'LOSE'; END IF;
 IF me = 'paper'    AND you = 'rock'     THEN RETURN 'WIN';  END IF;
 IF me = 'paper'    AND you = 'scissors' THEN RETURN 'LOSE'; END IF;
 IF me = 'scissors' AND you = 'paper'    THEN RETURN 'WIN';  END IF;
 IF me = 'scissors' AND you = 'rock'     THEN RETURN 'LOSE'; END IF;
 RAISE EXCEPTION 'Unknown entry! % and %', me, you;
END;
$$;

SELECT rps('Rock', 'Paper'), rps('Paper','Rock'), rps('Scissors','Scissors');

 rps  | rps | rps
------+-----+------
 LOSE | WIN | DRAW

A few notes about the function:

We marked the function as IMMUTABLE because it will always output the same answer given the same input, regardless of anything else happening in the database.
We immediately cast both arguments to their lowercase values so we don't have sto worry about what case gets passed in
Using RAISE DEBUG is a nice way to have output that is normally invisible, until you do SET client_min_messages = 'DEBUG';
The final RAISE EXCEPTION is a safety measure that one should always have to catch unexpected input.

Since we are comparing two items against each other, a custom operator would seem a nice option here. An operator is just a symbol that "operates" on one or two items near it. We will use the "spaceship" operator, which will then allow us to see the outcome of a battle with ease:

DROP OPERATOR if exists <=> (text, text);

CREATE OPERATOR <=> (
  leftarg  = text,
  rightarg = text,
  function = rps
);

SELECT 'Rock' <=> 'Paper', 'Paper'<=>'Rock', 'Scissors' <=> 'Scissors';

 ?column? | ?column? | ?column?
----------+----------+----------
 LOSE     | WIN      | DRAW

We can now return to our output, and use a CTE to build each part. We already mapped out the characters to the actual item played, so let's see the result for each round:

WITH x AS (
  SELECT *,
    CASE WHEN you='A' THEN 'Rock' WHEN you='B' THEN 'Paper' ELSE 'Scissors' END AS yourplay,
    CASE WHEN me ='X' THEN 'Rock' WHEN me ='Y' THEN 'Paper' ELSE 'Scissors' END AS myplay
  FROM aoc_day2
 )
, y AS (SELECT *, myplay <=> yourplay AS result FROM x)
SELECT * FROM y;

 you | me | yourplay |  myplay  | result
-----+----+----------+----------+--------
 C   | Z  | Scissors | Scissors | DRAW
 B   | Y  | Paper    | Paper    | DRAW
 C   | X  | Scissors | Rock     | WIN
 B   | Z  | Paper    | Scissors | WIN
 C   | Y  | Scissors | Paper    | LOSE
 B   | Y  | Paper    | Paper    | DRAW
 C   | Z  | Scissors | Scissors | DRAW
 C   | Z  | Scissors | Scissors | DRAW
 B   | Y  | Paper    | Paper    | DRAW
 B   | X  | Paper    | Rock     | LOSE

Next, we assign the two scores, one based on the result of the round, and one based on which item we used:

WITH x AS (
  SELECT *,
    CASE WHEN you='A' THEN 'Rock' WHEN you='B' THEN 'Paper' ELSE 'Scissors' END AS yourplay,
    CASE WHEN me ='X' THEN 'Rock' WHEN me ='Y' THEN 'Paper' ELSE 'Scissors' END AS myplay
  FROM aoc_day2
)
, y AS (SELECT *, myplay <=> yourplay AS result FROM x)
, z AS (SELECT *,
    ASCII(me)-87 AS shape_score,
    CASE WHEN result = 'WIN' THEN 6 WHEN result='DRAW' THEN 3 ELSE 0 END AS outcome_score
  FROM y
)
SELECT * FROM z;

 you | me | yourplay |  myplay  | result | shape_score | outcome_score
-----+----+----------+----------+--------+-------------+---------------
 C   | Z  | Scissors | Scissors | DRAW   |           3 |             3
 B   | Y  | Paper    | Paper    | DRAW   |           2 |             3
 C   | X  | Scissors | Rock     | WIN    |           1 |             6
 B   | Z  | Paper    | Scissors | WIN    |           3 |             6
 C   | Y  | Scissors | Paper    | LOSE   |           2 |             0
 B   | Y  | Paper    | Paper    | DRAW   |           2 |             3
 C   | Z  | Scissors | Scissors | DRAW   |           3 |             3
 C   | Z  | Scissors | Scissors | DRAW   |           3 |             3
 B   | Y  | Paper    | Paper    | DRAW   |           2 |             3
 B   | X  | Paper    | Rock     | LOSE   |           1 |             0

Finally, we tie it all together by adding up the scores for each round, then combining them into our final value as the solution to Day 2:

WITH x AS (
  SELECT *,
    CASE WHEN you='A' THEN 'Rock' WHEN you='B' THEN 'Paper' ELSE 'Scissors' END AS yourplay,
    CASE WHEN me ='X' THEN 'Rock' WHEN me ='Y' THEN 'Paper' ELSE 'Scissors' END AS myplay
  FROM aoc_day2
)
, y AS (SELECT *, myplay <=> yourplay AS result FROM x)
, z AS (
  SELECT ASCII(me)-87 AS shape_score,
    CASE WHEN result = 'WIN' THEN 6 WHEN result='DRAW' THEN 3 ELSE 0 END AS outcome_score
  FROM y
)
SELECT SUM(shape_score + outcome_score) FROM z;

 sum
-----
  52

We are not done yet! Unlike Day 1, Day 2 has two parts to it. In this new part, the meaning of the second entry has changed from what item we played, to what the outcome of the round should be. X means we lose, Y is a draw, and Z means we win. We can keep a lot of the code the same, and only adjust what we play:

WITH x AS (
  SELECT *,
    CASE WHEN you='A' THEN 'Rock' WHEN you='B' THEN 'Paper' ELSE 'Scissors' END AS yourplay
  FROM aoc_day2
)
,y AS (
  SELECT *,
    CASE WHEN me='Y' THEN yourplay
         WHEN me='X' AND you='A' THEN 'Scissors' WHEN me='X' AND you='B' THEN 'Rock' WHEN me='X' THEN 'Paper'
         WHEN me='Z' AND you='A' THEN 'Paper' WHEN me='Z' AND you='B' THEN 'Scissors' WHEN me='Z' THEN 'Rock'
    END AS myplay
  FROM x)
SELECT * FROM y;

 you | me | yourplay |  myplay
-----+----+----------+----------
 C   | Z  | Scissors | Rock
 B   | Y  | Paper    | Paper
 C   | X  | Scissors | Paper
 B   | Z  | Paper    | Scissors
 C   | Y  | Scissors | Scissors
 B   | Y  | Paper    | Paper
 C   | Z  | Scissors | Rock
 C   | Z  | Scissors | Rock
 B   | Y  | Paper    | Paper
 B   | X  | Paper    | Rock

Then we can plug in the rest of the CTE, with one small change being we need to get our shape_score from the new value, not the original input:

WITH x AS (
  SELECT *,
    CASE WHEN you='A' THEN 'Rock' WHEN you='B' THEN 'Paper' ELSE 'Scissors' END AS yourplay
  FROM aoc_day2
)
,y AS (
  SELECT *,
    CASE WHEN me='Y' THEN yourplay
         WHEN me='X' AND you='A' THEN 'Scissors' WHEN me='X' AND you='B' THEN 'Rock' WHEN me='X' THEN 'Paper'
         WHEN me='Z' AND you='A' THEN 'Paper' WHEN me='Z' AND you='B' THEN 'Scissors' WHEN me='Z' THEN 'Rock'
    END AS myplay
  FROM x)
,z AS (SELECT *, myplay <=> yourplay AS result FROM y)
,q AS (
  SELECT
    CASE WHEN myplay='Rock' THEN 1 WHEN myplay='Paper' THEN 2 ELSE 3 END AS shape_score,
    CASE WHEN result = 'WIN' THEN 6 WHEN result='DRAW' THEN 3 ELSE 0 END AS outcome_score
  FROM z
)
SELECT sum(shape_score + outcome_score) FROM q;

 sum
-----
  54

Done with both parts of Day 2! Onwards to Day 3...

Featured Products

All Crunchy Products

Crunchy Bridge

Cloud Partners

Industries

Use Cases

Integrations

How to Solve Advent of Code 2022 Using Postgres - Day 2

Spoiler Alert!

Hands on Tutorial

AOC Day 2