Tutorial Instructions

Advent of Code - 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:

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

Let's take a quick peek at the table as read in by Postgres:


SELECT * FROM aoc_day2 limit 10;

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

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
(1 row)

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 to 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
(1 row)

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

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

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  
-------
 11386
(1 row)

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

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  
-------
 13600
(1 row)

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

Loading terminal...

Featured Products

All Crunchy Products

Crunchy Bridge

Cloud Partners

Industries

Use Cases

Integrations

Advent of Code - Day 2