## DEV Community is a community of 642,858 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

loading...

# Advent Of Code 2020 in SQL – Day 3: “Toboggan Trajectory”

Samuel Nitsche
Curiosity-driven software-developer, 10x underpants. Striving for harm-reduction. We don't need more rockstars, we need more mentors.
Originally published at cleandatabase.wordpress.com on ・3 min read

Okay, this is a tougher one in SQL – but totally possible!

We have a repeating pattern of trees and need to calculate how many trees we will hit, starting from top left position, when we always go 1 down and 3 right.

To make it a little bit easier I will use PL/SQL to translate the input (again getting it from local webserver) into a Table with that layout:

LINE POS TREE
1 1 0
1 2 1
1 3 0
``````create table aoc_day3_input (
line integer not null,
pos integer not null,
tree number(1,0) not null,
primary key (line, pos)
);

create or replace package advent_of_code_day_3 as
procedure load_input;
end;
/

create or replace package body advent_of_code_day_3 as
procedure load_input as
l_request utl_http.req;
l_response utl_http.resp;
l_value varchar2(1024);
l_line integer := 1;
l_tree number(1,0) := 0;
begin
execute immediate 'truncate table aoc_day3_input';
begin
l_request := utl_http.begin_request('http://192.168.3.102/day_3_input.txt');
l_response := utl_http.get_response(l_request);
loop
utl_http.read_line(l_response, l_value, true);
for l_pos in 1..length(l_value) loop
if substr(l_value, l_pos, 1) = '#' then
l_tree := 1;
else
l_tree := 0;
end if;
insert into aoc_day3_input (line, pos, tree)
values ( l_line, l_pos, l_tree);
end loop;
l_line := l_line+1;
end loop;
exception
when utl_http.end_of_body then
utl_http.end_response(l_response);
end;
end;

end;
/

call advent_of_code_day_3.load_input();
commit;

select * from aoc_day3_input order by line, pos;
``````

With that data we can now use recursive WITH-clauses to loop through the input data:

``````with
max_pos as (
select max(pos) max_pos
from aoc_day3_input
),
step( line, pos, tree) as (
select line, pos, tree
from aoc_day3_input
where line = 1 and pos = 1
union all
select cur.line, cur.pos, cur.tree
from aoc_day3_input cur,
step prev,
max_pos mp
where cur.line = prev.line+1
and cur.pos =
case when prev.pos+3 > mp.max_pos then
mod(prev.pos+3, mp.max_pos)
else
prev.pos+3
end
)
select sum(tree) from step;
``````

To repeat the pattern, we have to check whether our current pos would be greater than the max position – an information we can join in with another WITH-clause – and if so, only take the rest.

Summarizing again is easy thanks to SQL.

Part 2 now wants us to do two more things:

• Calculate the trees hit for 5 different slopes
• Multiply all the 5 results

These are also two different challenges: For once, we have to bring in the different slopes with different right/down values. We can do this with another WITH-clause.

The second challenge is to multiply the results, which live in different rows. To overcome that, we can pivot the rows into columns – and columns can be multiplied easily.

``````with
max_pos as (
select max(pos) max_pos
from aoc_day3_input
),
slopes as (
select 1 id, 1 right, 1 down from dual union all
select 2 , 3 , 1 from dual union all
select 3 , 5 , 1 from dual union all
select 4 , 7 , 1 from dual union all
select 5 , 1 , 2 from dual
),
step( line, pos, tree, slope_id) as (
select line, pos, tree, slopes.id slope_id
from aoc_day3_input, slopes
where line = 1 and pos = 1
union all
select cur.line, cur.pos, cur.tree, slope.id
from aoc_day3_input cur,
step prev,
slopes slope,
max_pos mp
where
slope.id = prev.slope_id
and cur.line = prev.line+slope.down
and cur.pos =
case when prev.pos+slope.right > mp.max_pos then
mod(prev.pos+slope.right, mp.max_pos)
else
prev.pos+slope.right
end
),
slope_results as (
select
slope_id, sum(tree) trees_hit
from step
group by slope_id
)
select "1"*"2"*"3"*"4"*"5"
from slope_results
pivot (
sum(trees_hit)
for slope_id
in (1, 2, 3, 4, 5)
);
``````

Yay! This was fun.

You can find Part 1 and 2 (with scripted population of the first 9 lines of input-data) on Oracle LiveSQL.