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:
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.