Advent Of Code 2020 in SQL – Day 3: “Toboggan Trajectory”
Tags: adventofcode, sql
Published: 2020-12-08 11:23:44

---
title: Advent Of Code 2020 in SQL – Day 3: “Toboggan Trajectory”
published: true
date: 2020-12-03 16:45:33 UTC
tags: AdventOfCode,SQL
canonical_url: https://cleandatabase.wordpress.com/2020/12/03/advent-of-code-day-3-toboggan-trajectory/
series: Advent Of Code 2020 in SQL
---

Okay, [this is a tougher one](https://adventofcode.com/2020/day/3) 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](https://dev.to/pesse/advent-of-code-in-sql-and-pl-sql-if-necessary-940-temp-slug-1329172)) into a Table with that layout:

| LINE | POS | TREE |
| --- | --- | --- |
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 0 |

```sql
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](https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2) to loop through the input data:

```sql
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.

```sql
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](https://livesql.oracle.com/apex/livesql/s/k09m4z2v26ujcx6p7fmnqssjh).