aoc_2025_04.sql
edited
1-------------|
2-- Cleanup --|
3-------------|
4
5drop table if exists input;
6drop table if exists rolls;
7drop function if exists part_2;
8
9-----------|
10-- Setup --|
11-----------|
12
13create unlogged table input (
14 y serial primary key,
15 value text
16);
17\copy input (value) from 'input/2025/4.txt';
18
19create unlogged table rolls (
20 x int not null,
21 y int not null,
22 primary key (x, y)
23);
24
25
26-----------------|
27-- Parse input --|
28-----------------|
29
30insert into rolls (x, y)
31select
32 col.x,
33 row.y
34from input as row
35cross join
36 lateral
37 unnest(string_to_array(row.value, null))
38 with ordinality as col (char, x)
39where col.char = '@';
40
41------------|
42-- Part 1 --|
43------------|
44
45select count(*) as part_1
46from rolls as pos
47where (
48 select count(*) as count
49 from rolls as neighbor
50 where
51 neighbor.x between pos.x - 1 and pos.x + 1
52 and
53 neighbor.y between pos.y - 1 and pos.y + 1
54 and not
55 (pos.x = neighbor.x and pos.y = neighbor.y)
56) < 4;
57
58
59------------|
60-- Part 2 --|
61------------|
62
63-- Let's get procedural baybey
64-- I tried for a long time to get this into a recursive CTE,
65-- but couldn't find a way around the ban on outer joins
66-- on the recursive table
67
68create function part_2()
69returns int
70as $$
71declare
72 removed_total int = 0;
73 removed_round int = 1;
74begin
75 while removed_round > 0 loop
76 delete
77 from rolls as pos
78 where (
79 select count(*) as count
80 from rolls as neighbor
81 where
82 neighbor.x between pos.x - 1 and pos.x + 1
83 and
84 neighbor.y between pos.y - 1 and pos.y + 1
85 and not
86 (pos.x = neighbor.x and pos.y = neighbor.y)
87 ) < 4;
88
89 get diagnostics removed_round = row_count;
90 removed_total = removed_total + removed_round;
91 end loop;
92 return removed_total;
93end;
94$$ language plpgsql;
95
96select part_2();