70 lines
1.4 KiB
SQL
70 lines
1.4 KiB
SQL
create or replace table day03_data as
|
|
select bank
|
|
from read_csv(
|
|
'inputs/03/input.txt',
|
|
header = false,
|
|
columns = { 'bank': 'VARCHAR' }
|
|
);
|
|
create or replace table day03_test as
|
|
select bank
|
|
from read_csv(
|
|
'inputs/03/test.txt',
|
|
header = false,
|
|
columns = { 'bank': 'VARCHAR' }
|
|
);
|
|
|
|
with with_best as (
|
|
select bank,
|
|
coalesce((
|
|
select max(digit),
|
|
from generate_series(2, 9) as t(digit)
|
|
where contains(bank[:-2], digit::string) is true
|
|
), 1) as best
|
|
from day03_test
|
|
),
|
|
with_best_pos as (
|
|
select *,
|
|
instr(bank, best::string) as best_pos,
|
|
bank[best_pos+1:],
|
|
coalesce((
|
|
select max(digit)
|
|
from generate_series(2, 9) as t(digit)
|
|
where contains(bank[best_pos+1:], digit::string) is true
|
|
), 1) as second_best
|
|
from with_best
|
|
)
|
|
select sum(best * 10 + second_best) as part1
|
|
from with_best_pos
|
|
;
|
|
|
|
with recursive seq as (
|
|
select
|
|
bank,
|
|
12 as step,
|
|
'' as digits,
|
|
0 as pos,
|
|
bank[0:-12] as sub,
|
|
from day03_data
|
|
|
|
union all
|
|
|
|
select
|
|
s.bank,
|
|
s.step - 1 as new_step,
|
|
s.digits || max_digit::string as digits,
|
|
s.pos+instr(s.sub, max_digit::string) as new_pos,
|
|
s.bank[new_pos+1:-new_step] as sub,
|
|
from seq s
|
|
join lateral (
|
|
select max(digit) as max_digit
|
|
from generate_series(1,9) as t(digit)
|
|
where contains(s.sub, digit::string)
|
|
) as m
|
|
on s.step > 0
|
|
)
|
|
select sum(digits::hugeint) as total
|
|
from seq
|
|
where step = 0
|
|
;
|
|
|