aoc_latest.sql

finalise_for_part_2.sql

select
    group_concat(ingredient, ',')
from (
    select
        ingredient,
        allergen
    from
        named_allergens
    order by allergen
);

setup.sql

.load ../utils/split.dylib

create table input(line TEXT);

.mode csv
.separator \t
.import 21_real.txt input

create table named_allergens(allergen TEXT, ingredient TEXT);
create table all_allergens(food int, ingredient TEXT, allergen TEXT);

with

preprocess(food, ingredients, allergens) as (
    select
        input.rowid,
        substr(input.line, 0, instr(input.line," (contains")),
        replace(
            substr(input.line, instr(input.line," (contains")+11),
            ")",""
        )
    from
        input
),

split_ingredients(food, ingredient, allergens) as (
    select
        food,
        split.value,
        allergens
    from
        preprocess,
        split(preprocess.ingredients," ")
),

split_allergens(food, ingredient, allergen) as (
    select
        food,
        ingredient,
        split.value
    from
        split_ingredients,
        split(split_ingredients.allergens, ", ")
    where
        split.value != ''
)

insert into all_allergens
    select * from split_allergens
    union all
    select distinct food, ingredient, '' from split_allergens;

step.sql

create temporary table isolated_allergen as with

allergen_totals(allergen, total) as (
    select
        allergen,
        count(distinct food)
    from
        all_allergens
    where
        allergen != ''
    group by
        1
),

pairs(allergen, ingredient, total) as (
    select
        allergen,
        ingredient,
        count(*)
    from
        all_allergens
    where
        allergen != ''
    group by
        1, 2
),

candidate_pairs(allergen, ingredient) as (
    select
        pairs.allergen,
        pairs.ingredient
    from
        pairs,
        allergen_totals
    where
        allergen_totals.total = pairs.total
        and
        allergen_totals.allergen = pairs.allergen
),

isolated_allergen(allergen, ingredient) as (
    select
        allergen,
        ingredient
    from
        candidate_pairs
    group by
        1
    having
        count(allergen) = 1
) select * from isolated_allergen;

-- Keep track of discovered allergens
insert into named_allergens select * from isolated_allergen;

-- Get rid of them
delete from all_allergens
where
    ingredient in (select ingredient from isolated_allergen)
    or
    allergen in (select allergen from isolated_allergen);

select count(*) from all_allergens;