65 lines
1.3 KiB
SQL
65 lines
1.3 KiB
SQL
with recursive
|
|
|
|
raw (line) as (
|
|
select * from read_csv('data/05.in', header = false)
|
|
),
|
|
|
|
ranges (low, high) as (
|
|
select
|
|
cast(spl[1] as int64),
|
|
cast(spl[2] as int64)
|
|
from (
|
|
select split(line, '-') as spl
|
|
from raw
|
|
where contains(line, '-')
|
|
)
|
|
),
|
|
|
|
ingredients (id) as (
|
|
select cast(line as int64)
|
|
from raw
|
|
where line <> '' and not contains(line, '-')
|
|
),
|
|
|
|
part1 (solution) as (
|
|
select count(*) from ingredients
|
|
where exists (
|
|
select
|
|
low,
|
|
high
|
|
from ranges
|
|
where id between low and high
|
|
)
|
|
),
|
|
|
|
merged (low, high) as (
|
|
select * from ranges
|
|
|
|
union
|
|
|
|
select
|
|
r1.low,
|
|
case when r1.high < r2.high then r2.high else r1.high end
|
|
from merged r1, merged r2
|
|
where
|
|
(r1.low < r2.low or (r1.low = r2.low and r1.high < r2.high))
|
|
and not r1.high < r2.low and not r2.high < r1.low
|
|
),
|
|
|
|
part2 (solution) as (
|
|
select sum(m1.high - m1.low + 1) from merged m1
|
|
where not exists (
|
|
select *
|
|
from merged m2
|
|
where
|
|
(m1.high <> m2.high or m1.low <> m2.low)
|
|
and m1.high <= m2.high
|
|
and m1.low >= m2.low
|
|
)
|
|
)
|
|
|
|
select
|
|
part1.solution,
|
|
part2.solution
|
|
from part1, part2
|