aoc_2025_05.sql
edited
1-------------|
2-- Cleanup --|
3-------------|
4
5drop table if exists input_ranges;
6drop table if exists fresh;
7drop table if exists ingredients;
8
9-----------|
10-- Setup --|
11-----------|
12
13create unlogged table input_ranges (
14 low int8,
15 high int8
16);
17
18-- Creating a single multirange sped up the part 1 query by a factor of 10!
19create unlogged table fresh (
20 range int8multirange
21);
22
23create unlogged table ingredients (
24 id int8 primary key
25);
26
27-----------------|
28-- Parse input --|
29-----------------|
30
31-- I don't understand these sed things
32-- I just want the part before or after the empty line
33-- Client side commands are terminatd with a newline, so no breaky for me
34\copy input_ranges (low, high) from program'sed "/^$/Q" input/2025/5.txt' with delimiter '-'
35
36-- Insert values as inclusive ranges (postgres normalizes them to be half-open)
37-- because half-open ranges are objectively cooler.
38insert into fresh (range)
39select range_agg(int8range(range.low, range.high, '[]'))
40from input_ranges as range;
41
42-- We don't even need a temporary input table for the these
43\copy ingredients (id) from program 'sed "1,/^$/d" input/2025/5.txt';
44
45------------|
46-- Part 1 --|
47------------|
48
49select count(ingredient.id) as part_1
50from ingredients as ingredient
51inner join fresh on fresh.range @> ingredient.id;
52
53------------|
54-- Part 2 --|
55------------|
56
57-- Even for this one the multirange is useful because it does
58-- all the range merging for us!
59with flat (range) as (
60 select unnest(range) from fresh
61)
62select sum(upper(range) - lower(range)) as part_2
63from flat;