Advent Of Code 2020 in SQL – Day 5: “Binary Boarding”
Tags: adventofcode, sql
Published: 2020-12-08 11:27:02

---
title: Advent Of Code 2020 in SQL – Day 5: “Binary Boarding”
published: true
date: 2020-12-07 10:09:22 UTC
tags: AdventOfCode,SQL
canonical_url: https://cleandatabase.wordpress.com/2020/12/07/advent-of-code-day-5-binary-boarding/
series: Advent Of Code 2020 in SQL
---

Ah, the title already reveals what [the challenge](https://adventofcode.com/2020/day/5) will be about – and yes, I’m two days late, because it’s been weekend and weekend is family time, usually way from any computer device.

Our task is to get seat locations and IDs from the proprietary “`FBFBBFFRLR`” format – which is in reality a representation of binary numbers:

`FBFBBFF` = 7-bit number = 0101100 = 44  
`RLR` = 3-bit number = 101 = 5

The first step is to get the binary representation of the row and column data. I used a little helper function I wrote to get the input as `sys.odcivarchar2list` and into the `aoc_day5_input `table in the same way as I did with the previous inputs (downloading from local webserver).

```sql
with
  seat_data as (
    select
      substr(line, 1, 7) seat_row,
      substr(line, 8, 3) seat_col,
      line orig
    from aoc_day5_input
  ),
  binary_data as (
    select
      translate(seat_row, 'FB', '01') b_seat_row,
      translate(seat_col, 'LR', '01') b_seat_col
      from seat_data
  )
select * from binary_data
```

Now, we need to convert these binary-like strings into decimal numbers. Unfortunately, Oracle doesn’t provide a convenient function to do this, but it’s not very difficult to do it on our own: Take each char of `b_seat_row `and `b_seat_col `and multiply it with the according power of 2. For the first bit (which is the one with the highest value) it’s `power(2,6)`, for the `second power(2,5) `etc.

After that, we calculated the ID as requested and can kindly ask the database to give us the max value.

```sql
with
  seat_data as (
    select
      substr(line, 1, 7) seat_row,
      substr(line, 8, 3) seat_col,
      line orig
    from aoc_day5_input
  ),
  binary_data as (
    select
      translate(seat_row, 'FB', '01') b_seat_row,
      translate(seat_col, 'LR', '01') b_seat_col
      from seat_data
  ),
  decimal_data as (
    select
      substr(b_seat_row, 1, 1)*power(2,6)
        + substr(b_seat_row, 2, 1)*power(2,5)
        + substr(b_seat_row, 3, 1)*power(2,4)
        + substr(b_seat_row, 4, 1)*power(2,3)
        + substr(b_seat_row, 5, 1)*power(2,2)
        + substr(b_seat_row, 6, 1)*power(2,1)
        + substr(b_seat_row, 7, 1)
      dec_seat_row,
      substr(b_seat_col, 1, 1)*power(2,2)
        + substr(b_seat_col, 2, 1)*power(2,1)
        + substr(b_seat_col, 3, 1)
      dec_seat_col,
      b_seat_row,
      b_seat_col
      from binary_data
  ),
  data_with_id as (
    select
      dec_seat_row * 8 + dec_seat_col id,
      dec_seat_col,
      b_seat_row,
      b_seat_col
      from decimal_data
  )
select max(id)
from data_with_id;
```

Part 2 now requires us to find our seat-ID. It’s the only ID missing in the list, so we can use the analytic functions `lag` and `lead` to analyze the gaps between the IDs and find the one where there is a gap of 2.

```sql
with
  seat_data as (
    select
      substr(line, 1, 7) seat_row,
      substr(line, 8, 3) seat_col,
      line orig
    from aoc_day5_input
  ),
  binary_data as (
    select
      translate(seat_row, 'FB', '01') b_seat_row,
      translate(seat_col, 'LR', '01') b_seat_col
      from seat_data
  ),
  decimal_data as (
    select
      substr(b_seat_row, 1, 1)*power(2,6)
        + substr(b_seat_row, 2, 1)*power(2,5)
        + substr(b_seat_row, 3, 1)*power(2,4)
        + substr(b_seat_row, 4, 1)*power(2,3)
        + substr(b_seat_row, 5, 1)*power(2,2)
        + substr(b_seat_row, 6, 1)*power(2,1)
        + substr(b_seat_row, 7, 1)
      dec_seat_row,
      substr(b_seat_col, 1, 1)*power(2,2)
        + substr(b_seat_col, 2, 1)*power(2,1)
        + substr(b_seat_col, 3, 1)
      dec_seat_col,
      b_seat_row,
      b_seat_col
      from binary_data
  ),
  data_with_id as (
    select
      dec_seat_row * 8 + dec_seat_col id,
      dec_seat_col,
      b_seat_row,
      b_seat_col
      from decimal_data
  ),
  id_analysis as (
    select
      id,
      id-lag(id) over (order by id) gap_prev,
      lead(id) over (order by id)-id gap_next
    from data_with_id
  )
select
  prev.id+1 my_id
from id_analysis prev, id_analysis next
where prev.gap_next > 1
  and next.gap_prev > 1
  and next.id - prev.id = 2;

```

You can find this complete example in the newly created [github-repository](https://github.com/pesse/aoc-2020/blob/main/day_5.sql).

To get the sample data into Oracle LiveSQL is too annoying at the moment, so I won’t do it.