Advent of Code 2020, Day 9, Part One, SQL

advent_of_code_2020_day_9_part_one.sql

-- create temp table with sample input
insert into xmas_data (value) values (35), (20), (15), (25), (47), (40), (62), (55), (65), (95), (102), (117), (150), (182), (127), (219), (299), (277), (309), (576);

-- starter subquery
select
  v1.id v1id,
  v2.id v2id,
  v1.value,
  v2.value,
  v1.value + v2.value sum
from xmas_data v1, xmas_data v2
where
  v1.id != v2.id and
  abs(v1.id - v2.id) < 5;

-- solution for the sample input
select
  xmas_data.id,
  xmas_data.value,
  array_agg(sum_table.sum),
  array_agg(sum_table.sum) @> Array[xmas_data.value]
from xmas_data
cross join (
  select
    v1.id v1id,
    v2.id v2id,
    v1.value,
    v2.value,
    v1.value + v2.value sum
  from xmas_data v1, xmas_data v2
  where
    v1.id != v2.id and
    abs(v1.id - v2.id) < 5
) sum_table
where
  (xmas_data.id - sum_table.v1id) <= 5 and
  (xmas_data.id - sum_table.v1id) > 0 and
  (xmas_data.id - sum_table.v2id) <= 5 and
  (xmas_data.id - sum_table.v2id) > 0 and
  xmas_data.id > 5
group by xmas_data.id
order by xmas_data.id;