50 lines
1.5 KiB
SQL
50 lines
1.5 KiB
SQL
create or replace table day02_data as
|
|
with input1 as (
|
|
select unnest(split(column0, ',')) as ranges
|
|
from read_csv('inputs/02/input.txt', header=false, delim='\n'))
|
|
select
|
|
split(ranges, '-')[1]::long as range_start,
|
|
split(ranges, '-')[2]::long as range_end
|
|
from input1;
|
|
|
|
create or replace table day02_test as
|
|
with input1 as (
|
|
select unnest(split(column0, ',')) as ranges
|
|
from read_csv('inputs/02/test.txt', header=false, delim='\n'))
|
|
select
|
|
split(ranges, '-')[1]::long as range_start,
|
|
split(ranges, '-')[2]::long as range_end
|
|
from input1;
|
|
|
|
create or replace table day02_product_list as
|
|
select unnest(generate_series(range_start, range_end))::string as product_id
|
|
from day02_data;
|
|
|
|
select sum(product_id::long) as part1
|
|
from day02_product_list
|
|
where LENGTH(product_id) % 2 == 0
|
|
and product_id[:(LENGTH(product_id)/2)] == product_id[(LENGTH(product_id)/2)+1:];
|
|
|
|
with split_lengths as (
|
|
select product_id,
|
|
length(product_id) as len,
|
|
(
|
|
select array_agg(l)
|
|
from generate_series(1, length(product_id)-1) as t(l)
|
|
where length(product_id) % l == 0
|
|
) as lengths
|
|
from day02_product_list
|
|
),
|
|
with_chunks as (
|
|
select product_id, chunk_size,
|
|
(
|
|
select array_agg(substring(product_id, (j-1)*chunk_size+1, chunk_size))
|
|
from generate_series(1, ceil(length(product_id)/chunk_size)::int) as u(j)
|
|
) as chunks
|
|
FROM split_lengths, unnest(lengths) as t(chunk_size)
|
|
)
|
|
select sum(distinct product_id::long) as part2
|
|
from with_chunks
|
|
where length(array_distinct(chunks)) = 1
|
|
;
|