Advent of Code 2025- Day 5

Cafeteria

I accepted a small inefficiency in part 1 by reading through all of the data twice in order to come up with two data sources. One for the ranges of fresh ids, and the other the list of ids to check.

Counting the number of fresh ids was simply checking if each id fell between the low and high value of any fresh-ranges, and that’s accomplished easily enough by an inner join between the rows of ids to the rows of ranges. Since some ranges overlap it is possible for some ids to appear in multiple ranges, so I count the distinct ids returned from the join.

WITH
    ranges
    AS
        (SELECT TO_NUMBER(REGEXP_SUBSTR(str, '^[0-9]+')) lo, TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+$')) hi
           FROM advent.data2rows('advent2025-5')
          WHERE str LIKE '%-%'),
    ids
    AS
        (SELECT TO_NUMBER(str) id
           FROM advent.data2rows('advent2025-5')
          WHERE str NOT LIKE '%-%')
SELECT COUNT(DISTINCT id)
  FROM ranges JOIN ids ON ids.id BETWEEN ranges.lo AND ranges.hi;

For part 2 I didn’t need the ids, I just used the rows of ranges. Sorting the rows by the lo-value, I create a new lo of NULL if it would overlap a previous row. I check for overlap if the previous row’s hi-value is greater than or equal to the current row’s lo-value. I also extended the check by -1, thus combining ranges that didn’t overlap but were contiguous for example 1-3 and 4-6, can be considered just one range of 1-6 because there are no gaps between them.

I then group all such overlapping or contiguous rows by using LAST_VALUE ignoring NULLS. So the sample data would be grouped like this:

LO	HI	NEWLO	NEWLO2
3	5	3	3
10	14	10	10
12	18		10
16	20		10

With this data, consolidating the ranges is just getting the MAX(hi) value for each NEWLO2 grouping. Then subtract the lo from the hi (adding 1 to be end-value-inclusive) to get the number of fresh ids in the consolidated range, and then sum those differences to get the total count.

WITH ranges
    AS
        (SELECT TO_NUMBER(REGEXP_SUBSTR(str, '^[0-9]+')) lo, TO_NUMBER(REGEXP_SUBSTR(str, '[0-9]+$')) hi
           FROM advent.data2rows('advent2025-5')
          WHERE str LIKE '%-%')
SELECT SUM(diff)
  FROM (  SELECT MAX(hi) - newlo2 + 1 diff
            FROM (
                     SELECT LAST_VALUE(newlo IGNORE NULLS) OVER (ORDER BY lo) newlo2, hi
                       FROM (
                                SELECT lo, hi, CASE WHEN LAG(hi) OVER (ORDER BY lo) >= lo - 1 THEN NULL ELSE lo END newlo
                                  FROM ranges a
                            )
                 )
        GROUP BY newlo2);