91 lines
1.9 KiB
SQL
91 lines
1.9 KiB
SQL
create or replace table day04_data as
|
|
select
|
|
row_number() over() as row,
|
|
column0
|
|
from
|
|
read_csv(
|
|
'inputs/04/input.txt',
|
|
header = false
|
|
);
|
|
create or replace table day04_test as
|
|
select
|
|
row_number() over() as row,
|
|
column0
|
|
from
|
|
read_csv(
|
|
'inputs/04/test.txt',
|
|
header = false
|
|
);
|
|
|
|
create or replace table day04_clean as
|
|
select
|
|
row,
|
|
idx + 1 as col
|
|
from
|
|
day04_data
|
|
cross join unnest(range(length(column0))) as t(idx)
|
|
where substr(column0, idx + 1, 1) = '@';
|
|
|
|
create or replace table day04_neighbors as
|
|
select
|
|
c.row * 1000 + c.col as node,
|
|
a.row * 1000 + a.col as neighbor
|
|
from day04_clean as c
|
|
left join day04_clean as a
|
|
on a.row between (c.row - 1) and (c.row + 1)
|
|
and a.col between (c.col - 1) and (c.col + 1)
|
|
;
|
|
|
|
with neighbors_count as (
|
|
select node, count(1) as nb_neighbors
|
|
from day04_neighbors
|
|
group by node
|
|
)
|
|
select count_if(nb_neighbors <= 4) as part1
|
|
from neighbors_count
|
|
;
|
|
|
|
with recursive seq as (
|
|
with agg as (
|
|
select
|
|
node,
|
|
count(1) neighbors
|
|
from day04_neighbors
|
|
group by node
|
|
having neighbors > 4
|
|
)
|
|
select
|
|
node,
|
|
s.neighbor,
|
|
(select count(distinct node) from agg) as remaining,
|
|
0 as step
|
|
from day04_neighbors as s
|
|
inner join agg as a1 using (node)
|
|
inner join agg as a2 on a2.node = s.neighbor
|
|
|
|
union all
|
|
|
|
(
|
|
with agg2 as (
|
|
select node, step, count(distinct neighbor) as neighbors
|
|
from seq
|
|
group by node, step
|
|
having step = (select max(step) from seq)
|
|
and neighbors > 4
|
|
)
|
|
select
|
|
s.node,
|
|
s.neighbor,
|
|
(select count(distinct node) from agg2) as remaining,
|
|
s.step + 1 as step
|
|
from seq s
|
|
inner join agg2 as a1 using(node)
|
|
inner join agg2 as a2 on a2.node = s.neighbor
|
|
where (
|
|
select count(distinct node) from agg2) < (select min(remaining) from seq
|
|
)
|
|
)
|
|
)
|
|
select (select count(distinct node) from day04_neighbors) - (select min(remaining) from seq) as part2;
|
|
|