79 lines
1.9 KiB
SQL
79 lines
1.9 KiB
SQL
create or replace table day06_data as
|
|
select row_number() over () as line, column0
|
|
from read_csv('inputs/06/input.txt', header=false, delim='\n');
|
|
create or replace table day06_test as
|
|
select row_number() over () as line, column0
|
|
from read_csv('inputs/06/test.txt', header=false, delim='\n');
|
|
|
|
|
|
create or replace table day06_input as
|
|
with raw as (
|
|
select line as y,
|
|
array_filter(split(column0, ' '), lambda x: x != '') as s
|
|
from day06_data
|
|
), splitted as (
|
|
select x, y, elem
|
|
from raw,
|
|
unnest(s) with ordinality as u(elem, x)
|
|
), rotated as (
|
|
select array_agg(elem order by y) as rot
|
|
from splitted
|
|
group by x
|
|
)
|
|
select rot[-1] as op, list_apply(rot[:-2], lambda x: x::int) as num
|
|
from rotated;
|
|
|
|
select sum(
|
|
case when op = '+' then list_sum(num)
|
|
when op = '*' then list_product(num)
|
|
else 0
|
|
end
|
|
)::long as part1
|
|
from day06_input;
|
|
|
|
create or replace table day06_input2 as
|
|
with rotated as (
|
|
select
|
|
x,
|
|
array_agg(elem order by line) rot
|
|
from day06_data,
|
|
unnest(split(column0, '')) with ordinality as u(elem, x)
|
|
group by x
|
|
order by x
|
|
), cleaned as (
|
|
select
|
|
x,
|
|
any_value(rot[-1]) as op,
|
|
reverse(string_agg(n, '')) as num
|
|
from rotated, unnest(array_filter(rot[:-2], lambda x: x != ' ')) as u(n)
|
|
group by x
|
|
), with_breaks as (
|
|
select
|
|
x, op, num::int as num,
|
|
case when lag(x) over (order by x) = x - 1 then 0 else 1 end as is_break
|
|
from cleaned
|
|
), with_groups as (
|
|
select
|
|
x, op, num,
|
|
sum(is_break) over (order by x rows unbounded preceding) as group_id
|
|
from with_breaks
|
|
), grouped as (
|
|
select
|
|
group_id,
|
|
max(op) as op,
|
|
array_agg(num) as nums
|
|
from with_groups
|
|
group by group_id
|
|
)
|
|
select *
|
|
from grouped;
|
|
|
|
select sum(
|
|
case when op = '+' then list_sum(nums)
|
|
when op = '*' then list_product(nums)
|
|
else 0
|
|
end
|
|
)::long as part2
|
|
from day06_input2;
|
|
|