aoc-22-day3.sql

create table aoc_rucksack(
  rucksack varchar2(255) primary key
) inmemory;

create table aoc_item_priorities(
  item char(1) primary key,
  priority number
) inmemory;

insert into aoc_item_priorities(item, priority)
select 'a', 1 from dual union all
  select 'b', 2 from dual union all
  select 'c', 3 from dual union all
  select 'd', 4 from dual union all
  select 'e', 5 from dual union all
  select 'f', 6 from dual union all
  select 'g', 7 from dual union all
  select 'h', 8 from dual union all
  select 'i', 9 from dual union all
  select 'j', 10 from dual union all
  select 'k', 11 from dual union all
  select 'l', 12 from dual union all
  select 'm', 13 from dual union all
  select 'n', 14 from dual union all
  select 'o', 15 from dual union all
  select 'p', 16 from dual union all
  select 'q', 17 from dual union all
  select 'r', 18 from dual union all
  select 's', 19 from dual union all
  select 't', 20 from dual union all
  select 'u', 21 from dual union all
  select 'v', 22 from dual union all
  select 'w', 23 from dual union all
  select 'x', 24 from dual union all
  select 'y', 25 from dual union all
  select 'z', 26 from dual union all
  select 'A', 27 from dual union all
  select 'B', 28 from dual union all
  select 'C', 29 from dual union all
  select 'D', 30 from dual union all
  select 'E', 31 from dual union all
  select 'F', 32 from dual union all
  select 'G', 33 from dual union all
  select 'H', 34 from dual union all
  select 'I', 35 from dual union all
  select 'J', 36 from dual union all
  select 'K', 37 from dual union all
  select 'L', 38 from dual union all
  select 'M', 39 from dual union all
  select 'N', 40 from dual union all
  select 'O', 41 from dual union all
  select 'P', 42 from dual union all
  select 'Q', 43 from dual union all
  select 'R', 44 from dual union all
  select 'S', 45 from dual union all
  select 'T', 46 from dual union all
  select 'U', 47 from dual union all
  select 'V', 48 from dual union all
  select 'W', 49 from dual union all
  select 'X', 50 from dual union all
  select 'Y', 51 from dual union all
  select 'Z', 52 from dual;


create or replace view aoc_rucksack_compartment_one_items
as
select 
  r.rucksack
, i.column_value as item
from aoc_rucksack r
cross join apex_string.split(substr(r.rucksack,1,length(r.rucksack) / 2), null) i;

create or replace view aoc_rucksack_compartment_two_items
as
select 
  r.rucksack
, i.column_value as item
from aoc_rucksack r
cross join apex_string.split(substr(r.rucksack,length(r.rucksack) / 2 + 1), null) i;

create or replace view aoc_rucksack_item_match
as
select distinct o.rucksack, o.item, p.priority
from aoc_rucksack_compartment_one_items o
join aoc_rucksack_compartment_two_items t on o.rucksack = t.rucksack and o.item = t.item
join aoc_item_priorities p on o.item = p.item
;

select sum(priority)
from aoc_rucksack_item_match;

--- Part 2 ---
alter table aoc_rucksack add(
  badge_group number
);

-- create groups in blocks of 3
-- risky not ordering but oh well
update aoc_rucksack
set badge_group = ceil(rownum/3)
;

create table aoc_badge_group_items(
    rucksack varchar(255),
    badge_group number,
    item char(1)
) organization index;

insert into aoc_badge_group_items
select distinct
  r.rucksack
, r.badge_group
, i.column_value as item
from aoc_rucksack r
cross join apex_string.split(r.rucksack, null) i

select sum(priority)
from(
    select i.badge_group, max(p.priority) as priority, max(i.item) as item 
    from aoc_badge_group_items i
    join aoc_item_priorities p on i.item = p.item
    group by i.badge_group, i.item
    having count(*) = 3
    order by i.badge_group
);

drop table aoc_badge_group_items;
drop table aoc_rucksack;
drop table aoc_item_priorities;
drop view aoc_rucksack_compartment_one_items;
drop view aoc_rucksack_compartment_two_items;
drop view aoc_rucksack_item_match;