Concept · SQL & Querying

Merging overlapping ranges is islands-on-intervals — a range starts a new island only if it begins after the running max of all previous ends.

The naive instinct — compare each row to the previous row's end — fails on containment (after (1,9), the range (2,3) ends earlier, and (8,10) must compare against 9, not 3); the running maximum of ends carries the furthest frontier forward. From there it's the session_running_flag machinery verbatim: boundary flag → running sum → group. Touching-vs-overlapping is a boundary-semantics choice (>= merges abutting ranges).

See it break

s	e
1	9
2	3
8	10

The trap

WITH
  p AS (
    SELECT
      s,
      e,
      LAG (e) OVER (
        ORDER BY
          s ) AS prev_end
    FROM
      bk
  ),
  g AS (
    SELECT
      s,
      e,
      SUM(
        CASE
          WHEN prev_end IS NULL
          OR s > prev_end THEN 1
          ELSE 0
        END
      ) OVER (
        ORDER BY
          s
      ) AS island
    FROM
      p
  )
SELECT
  MIN(s) AS start,
  MAX(e) AS END
FROM
  g
GROUP BY
  island
ORDER BY
  start;

The fix

WITH
  f AS (
    SELECT
      s,
      e,
      MAX(e) OVER (
        ORDER BY
          s ROWS BETWEEN UNBOUNDED PRECEDING
          AND 1 PRECEDING
      ) AS prev_max
    FROM
      bk
  ),
  g AS (
    SELECT
      s,
      e,
      SUM(
        CASE
          WHEN prev_max IS NULL
          OR s > prev_max THEN 1
          ELSE 0
        END
      ) OVER (
        ORDER BY
          s
      ) AS island
    FROM
      f
  )
SELECT
  MIN(s) AS start,
  MAX(e) AS END
FROM
  g
GROUP BY
  island
ORDER BY
  start;

The trap

810

The fix

110

The bookings (1,9),(2,3),(8,10) merged into two blocks instead of one — comparing each range to the previous row's end let the contained (2,3) reset the frontier to 3, so (8,10) wrongly started a new island; the running MAX of all previous ends keeps the frontier at 9.

The rule

Running-max frontier + flag + sum; the abutting-ranges decision stated.

Shows up elsewhere

Sessionize by running-SUM of flags Off-by-one at boundaries

Try one →Prove it in practice →