bert/adventofcode
1
0
Fork 0
mirror of https://github.com/bertptrs/adventofcode.git synced 2025-12-25 12:50:32 +01:00
Code Issues Projects Releases Wiki Activity
Files
master
adventofcode/2025/day04/solve.sql
Bert Peters 0a7ec71a97 Very ugly part 2
2025-12-04 22:35:12 +01:00

760 lines
19 KiB
SQL
Raw Permalink Blame History

-- First import raw data into a table so we can work
CREATE TABLE raw_data(line_data VARCHAR NOT NULL);
.import --csv './sample.txt' raw_data
-- Then use the auto-incrementing ID to add a y coordinate
CREATE TABLE grid_lines(
y INTEGER PRIMARY KEY,
line_data VARCHAR NOT NULL
);
INSERT INTO grid_lines(line_data)
SELECT line_data
FROM raw_data;
-- Now create a table to hold the paper rolls
CREATE TABLE rolls(
x INTEGER NOT NULL,
y INTEGER NOT NULL,
PRIMARY KEY (x, y)
);
WITH RECURSIVE cte AS (
SELECT y,
1 x,
line_data,
substr(line_data, 1, 1) c
FROM grid_lines
UNION ALL
SELECT y,
x + 1,
line_data,
substr(line_data, x + 1, 1)
FROM cte
WHERE x <= length(line_data)
)
INSERT INTO rolls
SELECT x,
y
FROM cte
WHERE c = '@';
-- Now compute part 1
SELECT COUNT(*) as part1
FROM rolls r
WHERE (
SELECT COUNT(*) - 1
FROM rolls o
WHERE o.x >= r.x - 1
AND o.x <= r.x + 1
AND o.y >= r.y - 1
AND o.y <= r.y + 1
) < 4;
--- Create a scratch table where we're going to delete rolls from
CREATE TABLE rolls2(
x INTEGER NOT NULL,
y INTEGER NOT NULL,
PRIMARY KEY (x, y)
);
INSERT INTO rolls2
SELECT *
FROM rolls;
-- Delete the outer rolls of paper 77 times. This turns out to be enough.
-- I tried really hard not to do this.
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
DELETE FROM rolls2
WHERE (
SELECT COUNT(*) - 1
FROM rolls2 o
WHERE o.x >= rolls2.x - 1
AND o.x <= rolls2.x + 1
AND o.y >= rolls2.y - 1
AND o.y <= rolls2.y + 1
) < 4;
-- See how many we've deleted
SELECT (
SELECT COUNT(*)
FROM rolls
) - (
SELECT COUNT(*)
FROM rolls2
) as part2;
Reference in New Issue View Git Blame Copy Permalink