SELECT solution FROM aoc WHERE year = 2022

Every year Advent of Code happens. Every year I'm not super enthusiastic to actually solve those challenges. Every year I still start (and then maybe stop after some days).

2022 was similar. I don't have a new programming language to learn and I still write enough code day-by-day that more coding is not really necessary. But yet sometimes it can be kind of nice to have a small challenge with a clear solution to go for and so I set myself some rules and went ahead.

My own rules

These are my rules. I break them when I feel like it.

Sneak peek of day 1

SELECT solution FROM aoc WHERE year = 2022 AND day = 1;

I plan to release my solutions publicly, but I haven't yet. So for now you get my solution to day 1:

CREATE TABLE data(raw);
.import input01.txt data
WITH
groups AS (
  SELECT rowid, ROW_NUMBER() OVER (ORDER BY rowid) cat FROM data WHERE raw = ''
),
elves AS (
  SELECT
    COALESCE(
      (SELECT cat FROM groups WHERE data.rowid < groups.rowid LIMIT 1),
      (SELECT cat + 1 FROM groups ORDER BY cat DESC LIMIT 1)
    ) AS elf,
    raw as calories
  FROM data WHERE raw != ''
),
part1 AS (
  SELECT
    elf,
    SUM(calories) AS total_calories
  FROM elves
  GROUP BY 1
  ORDER BY 2 DESC
)

--SELECT * FROM part1;
SELECT SUM(total_calories) as best_3 FROM (
    SELECT * FROM part1 LIMIT 3
);

Runnable as:

sqlite3 < aoc01.sql

(Oh, another rule: It needs to run as sqlite3 < script.sql)