Advent of Code, day 1, in pure MySQL

aoc_2022_day01.sql

CREATE TABLE day01 (elf int auto_increment primary key, calories JSON);

LOAD DATA LOCAL INFILE 'day01.txt' INTO TABLE day01 
LINES TERMINATED BY '\n\n' (@cal) 
SET calories = CONCAT('[', TRIM(TRAILING ',' FROM REPLACE(@cal, '\n', ',')), ']');

CREATE VIEW calories AS SELECT SUM(cal) AS cal 
FROM day01 
JOIN JSON_TABLE(calories, '$[*]' COLUMNS(cal int path '$')) jt GROUP BY elf ORDER BY cal DESC;

SELECT SUM(cal) AS part1 FROM (SELECT cal FROM calories LIMIT 1) dt;
SELECT SUM(cal) AS part2 FROM (SELECT cal FROM calories LIMIT 3) dt;