Advent of Code Day 5

Decided to try to go with pure PostgreSQL for this one.

Part 1 was easy. You are given a list of available ingredient id’s from the elven cafeteria, and a list of ranges of “fresh” ingredients. Determine which available ingredients are fresh.

Fresh Ranges:
3-5
10-14
16-20
12-18
Available:
1
5
8
11
17
32

I made 2 tables, fresh_ingredients and available_ingredients, and from there it’s a simple query:

SELECT ai.* FROM available_ingredients ai WHERE
(SELECT COUNT(*) FROM fresh_ingredients
WHERE ingredient_id_range_min <= ai.ingredient_id
AND ingredient_id_range_max >= ai.ingredient_id) > 0

Part 2 asks you to disregard the available ingredients and get a count of all fresh ingredients from the given ranges. Also a simple query, right?

SELECT COUNT(DISTINCT gs.ingredient_id)
FROM GENERATE_SERIES(
(SELECT MIN(ingredient_id_range_min) FROM fresh_ingredients),
(SELECT MAX(ingredient_id_range_max) FROM fresh_ingredients)) as gs(ingredient_id)
WHERE (SELECT COUNT(*) FROM fresh_ingredients
WHERE ingredient_id_range_min <= gs.ingredient_id
AND ingredient_id_range_max >= gs.ingredient_id) > 0

Wrong!

Theoretically, that query can solve it. Except the ranges they give you are things like: 377649615919740 – 379699191722247, which gives you 2,049,575,802,507 fresh id’s. For one range.

I ran out of disk space on my 1TB laptop.

NEW PLAN: Need to order the ranges starting with the lowest starting id, loop through each one and merge them. Also, have a calculate column figure out the difference in each range (we don’t care about each ingredient, just how many).

ALTER TABLE fresh_ingredients_merged
ADD COLUMN ingredient_id_range_diff BIGINT
GENERATED ALWAYS AS (ingredient_id_range_max - ingredient_id_range_min + 1) STORED;

Now the “merging” logic. This is what we’re aiming for:

-- Loop through ranges
-- If the new start number falls within an existing range,
-- Alter the existing end number to be the max of the new end number and existing
--
-- If the new end number falls within an existing range,
-- Alter the existing start number to be the min of the new start number and existing
--
-- if the new start AND end number fall within existing range, do nothing
--
-- if neither new start or end numbers fall within existing range, add a new row

Here’s the final working query. PS. I can’t fucking believe this worked. PPS. I know this could have been much simpler in Python or something but this is the life we’ve chosen!

DO
$do$
DECLARE
  i record;
BEGIN 
  FOR i IN SELECT * FROM fresh_ingredients ORDER BY ingredient_id_range_min LOOP
	-- If the new start number falls within an existing range,
	-- Alter the existing end number to be the max of the new end number and existing
	--
	-- If the new end number falls within an existing range,
	-- Alter the existing start number to be the min of the new start number and existing
	--
	--   if the new start AND end number fall within existing range, do nothing
	--
	-- if neither new start or end numbers fall within existing range, add a new row
	IF (SELECT COUNT(*) FROM fresh_ingredients_merged fim
		WHERE fim.ingredient_id_range_min <= i.ingredient_id_range_min
		AND fim.ingredient_id_range_max >= i.ingredient_id_range_max) > 0 THEN -- If BOTH fall in range
		-- Do nothing
	ELSIF (SELECT COUNT(*) FROM fresh_ingredients_merged fim
		WHERE fim.ingredient_id_range_min <= i.ingredient_id_range_min
		AND fim.ingredient_id_range_max >= i.ingredient_id_range_min) > 0 THEN -- If the min false in range
		    UPDATE fresh_ingredients_merged
		    SET ingredient_id_range_max = GREATEST(ingredient_id_range_max, i.ingredient_id_range_max)
		    WHERE ingredient_id_range_min <= i.ingredient_id_range_min 
			AND ingredient_id_range_max >= i.ingredient_id_range_min;
	ELSEIF (SELECT COUNT(*) FROM fresh_ingredients_merged fim
		WHERE fim.ingredient_id_range_min <= i.ingredient_id_range_max
		AND fim.ingredient_id_range_max >= i.ingredient_id_range_max) > 0 THEN -- If the max falls in range
			UPDATE fresh_ingredients_merged
		    SET ingredient_id_range_min = LEAST(ingredient_id_range_min, i.ingredient_id_range_min)
		    WHERE ingredient_id_range_min <= i.ingredient_id_range_max 
			AND ingredient_id_range_max >= i.ingredient_id_range_max;
	ELSE -- NOTHING falls in range
		INSERT INTO fresh_ingredients_merged(ingredient_id_range_min, ingredient_id_range_max)
		VALUES (i.ingredient_id_range_min, i.ingredient_id_range_max);
	END IF;
   END LOOP;
END;
$do$;