Advent of Code - Day 2
Tags: sql, beginners, programming, adventofcode
Published: 2022-12-02 21:51:31

The [second challenge](https://adventofcode.com/2022/day/2) of the [Advent of Code 2022](https://adventofcode.com) is pretty straightforward with SQL. In summary the task is to use some starting values and transform those into a numeric value using a lookup table, and then calculate the sum of all the values you get. If you are familiar with relational databases this should sound like a [`JOIN`](https://learn.microsoft.com/sql/relational-databases/performance/joins?view=sql-server-ver16) operation to get the lookup value and a [`GROUP BY`](https://learn.microsoft.com/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16) to get the results. 

The background story is that you are playing Tic-Tac-Toe with the elves. You are given an _encrypted strategy guide_ that you have to follow if you want to win.

Let's start importing the input. As yesterday I've pasted the input  values in a query and then I'm using [`STRING_SPLIT`](https://learn.microsoft.com/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver16) to move everything into a more comfortable table:

```sql
declare @input varchar(max) = 'B Y
A Y
B Z
...
A Y';

drop table if exists dbo.ch02_input;
with cte as
(
    select replace(value, char(10), '') as [round] from string_split(@input, char(13))
)
select 
    identity(int, 1, 1) as id,
    left([round], 1) as [opponent], 
    right([round], 1) as [player] 
into 
    [ch02_input]
from cte;
```

Full script is available on GitHub here: [day-02/00-setup.sql](https://github.com/yorek/aoc-2022/blob/main/day-02/00-setup.sql)

## Part 1

In part one you have to assign to each shape a value. I built the set on the fly using the [Row Constructors](https://learn.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql?view=sql-server-ver16)

```sql
select 
        * 
from 
	(values
		('A', 'Rock', 1),
		('Y', 'Paper', 2),
		('B', 'Paper', 2),
		('X', 'Rock', 1),
		('C', 'Scissors', 3),
		('Z', 'Scissors', 3)
	) decode(code, [shape], [value])		
```

and then all I had to do was join the above set with the input table, to convert the shapes into the associated value. I stored the result into the `#result` temporary table.

The last step to complete the task is to calculate if I won, tied, or lost each round. While I'm sure there are better ways to do that, given that the number of combinations is extremely limited, I went for a super simple solution, using the [`CASE`](https://learn.microsoft.com/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver16) statement (I'm really all in for [KISS](https://en.wikipedia.org/wiki/KISS_principle) approach):

```sql
select 
    *,
    case 
        when opponent_shape = player_shape then 3 -- Tie
        when opponent_shape = 'Rock' and player_shape = 'Paper' then 6 -- Won
        when opponent_shape = 'Rock' and player_shape = 'Scissors' then 0  -- Lost
        when opponent_shape = 'Paper' and player_shape = 'Rock' then 0 -- Lost
        when opponent_shape = 'Paper' and player_shape = 'Scissors' then 6  -- Won
        when opponent_shape = 'Scissors' and player_shape = 'Paper' then 0 -- Lost
        when opponent_shape = 'Scissors' and player_shape = 'Rock' then 6 -- Won
    end as outcome
from 
    #rounds;
```

Now, to calculate the overall score I did I just need to sum all my games:

```sql
select sum(player_value + outcome) from #results
```

Part 1, done. Find the full script here: [day-02/01-part1.sql](https://github.com/yorek/aoc-2022/blob/main/day-02/01-part1.sql)

## Part 2

In part two you discover that you didn't really decoded the original encrypted strategy guide. If fact, the X, Y and Z letter tells you not which shape you should play, but what should be the outcome of that game: "X means you need to lose, Y means you need to end the round in a draw, and Z means you need to win."

To solve the challenge then, I only needed to transform the X, Y and Z into the related A, B and C, based on the given logic. Again, with a CASE statement is pretty easy:

```sql
select 
    e.g.*,  
    case
        when player = 'Y' then opponent -- Must tie
        when player = 'X' then -- Must lose
            case opponent
                when 'A' then 'C'
                when 'B' then 'A'
                when 'C' then 'B'
            end
        when player = 'Z' then -- Must win
            case opponent 
                when 'A' then 'B'
                when 'B' then 'C'
                when 'C' then 'A'
            end
    end as player_decoded
from 
    dbo.ch02_input as eg
order by
    eg.id;
```

With these results I can just then apply the same queries used in Part One to calculate the round results and then get the overall result points. The full script for part two is here: [day-02/02-part2.sql](https://github.com/yorek/aoc-2022/blob/main/day-02/02-part2.sql)

## Additional notes

With such small datasets performances are almost never an issue. If you were to use a much larger dataset, say 100 times bigger than this, I would suggest three things to make sure you'll get the best performances possible

1. If you can, use numbers - integers - as identifiers. Those are much faster when aggregations are required. Strings are *really* expensive from a CPU perspective. 
2. If you cannot use numbers as identifiers for any reason, make sure you create columns or operate on string using a _binary_ collation. That will make string comparisons (and thus aggregations) much faster as the engine doesn't have to take into account casing, accents and so on. A collation like `Latin1_General_BIN2` is your friend when a string is used as id. ([Binary Collations](https://learn.microsoft.com/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#Binary-collations), [Colummn-level Collations](https://learn.microsoft.com/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#Column-level-collations), [Expression-level Collations](https://learn.microsoft.com/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#Expression-level-collations))
3. Use the [columnstore indexes]
(https://learn.microsoft.com/ql/relational-databases/indexes/columnstore-indexes-overview?view=sql-server-ver16) whenever you need to boost the aggregation performance.