In the context of consolidating overlapping contractual coverage periods into effective continuous blocks, how would you merge these intervals into disjoint, non-overlapping ranges using strictly ANSI SQL window functions without procedural loops?

Answer to the question

History of the question. The need to consolidate overlapping temporal intervals originates from Allen’s interval algebra (1983) and early relational database research into temporal databases. Insurance systems, hotel reservation platforms, and resource scheduling applications frequently encounter this challenge when multiple coverage periods, bookings, or maintenance windows overlap and require normalization into disjoint, contiguous blocks for accurate availability reporting or billing. Unlike simple aggregation, this operation demands understanding order and continuity, making it a standard test of advanced ANSI SQL window function mastery.

The problem. Given a table of date ranges defined by start_date and end_date columns, the objective is to merge all overlapping or adjacent intervals into a minimal set of non-overlapping ranges. A procedural approach would maintain a running buffer, comparing each row to the current merged range, but this violates SQL’s set-based paradigm. The core difficulty lies in identifying “islands” of continuity without self-joins or recursive CTEs, particularly when transitive overlaps exist (range A overlaps B, B overlaps C, though A and C do not directly touch).

The solution. Utilize ANSI SQL window functions to detect the start of each new island by comparing the current row’s start_date against the maximum end_date of all preceding rows within the same partition. When start_date exceeds the prior maximum end date, a new island begins; otherwise, the current row extends the existing island. Assign a running total of these “break” flags as an island_id, then group by this identifier to compute the consolidated min(start_date) and max(end_date). This approach achieves O(n log n) complexity through single-pass sorting and aggregation.

Situation from life

Problem description. A multinational healthcare provider maintained a claims processing database where patients held multiple overlapping insurance policies—primary coverage from January 1 to March 31, secondary from February 15 to April 15, and tertiary starting May 1. The existing system generated duplicate claim rejections because it treated these as separate active periods rather than one continuous coverage block from January 1 to April 15 followed by the May extension. The business required a consolidated view to enforce “no duplicate payment” rules while preserving audit trails of original policy records.

Solution 1: Procedural cursor-based iteration. An initial proposal used a stored procedure with a cursor ordered by start_date, maintaining variables @current_start and @current_end. For each row, if start_date ≤ @current_end, the code updated @current_end to max(@current_end, end_date); otherwise, it emitted the current range and reset the variables. Pros: Conceptually straightforward for developers with imperative backgrounds; easy to debug step-by-step. Cons: Requires PL/pgSQL or T-SQL procedural extensions; executes row-by-row with O(n) memory but poor I/O performance; violates the requirement for pure declarative ANSI SQL that can run on any compliant engine.

Solution 2: Self-join with transitive closure detection. Another approach performed a self-join t1 JOIN t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date to find immediate overlaps, then used a recursive CTE to walk the overlap graph and identify connected components. Pros: Handles complex transitive relationships theoretically correctly without window functions. Cons: Generates O(n²) intermediate rows before recursion; computationally explosive for large datasets; relies on recursive CTEs which, while ANSI SQL standard, are less performant than window functions for this specific linear ordering problem.

Solution 3: Window function gap detection (chosen). The team implemented a pure window function solution: flag is_new_island = CASE WHEN start_date > MAX(end_date) OVER (PARTITION BY patient_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END, then computed island_id = SUM(is_new_island) OVER (PARTITION BY patient_id ORDER BY start_date). Final aggregation grouped by patient_id, island_id. Pros: Single-pass execution leveraging ANSI SQL standard syntax; O(n log n) complexity limited by sorting; handles transitive overlaps implicitly through the running maximum. Cons: Requires careful handling of NULL end dates (indefinite coverage) and adjacent interval semantics (whether touching ranges merge).

Result. The deployment consolidated 2.3 million policy records into 890,000 continuous coverage blocks in under 12 seconds on standard hardware, replacing a 45-minute cursor-based batch job. The query was encapsulated as a view, enabling real-time eligibility checks and eliminating 99% of duplicate claim rejections during the subsequent quarter.

WITH coverage_flags AS (
    SELECT patient_id, start_date, end_date,
           CASE 
             WHEN start_date > MAX(end_date) OVER (
                 PARTITION BY patient_id 
                 ORDER BY start_date 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ) THEN 1 
             ELSE 0 
           END AS is_new_island
    FROM insurance_periods
),
islands AS (
    SELECT patient_id, start_date, end_date,
           SUM(is_new_island) OVER (
               PARTITION BY patient_id 
               ORDER BY start_date
           ) AS island_id
    FROM coverage_flags
)
SELECT patient_id, 
       MIN(start_date) AS consolidated_start,
       MAX(end_date) AS consolidated_end
FROM islands
GROUP BY patient_id, island_id;

What candidates often miss

How do you handle adjacent intervals that touch at endpoints, such as [January 1-10] and [January 11-20], and what predicate modification is required?

Candidates often use a strict inequality start_date > previous_end_date, which treats adjacent intervals as separate islands. For healthcare coverage or continuous scheduling, adjacent periods usually represent uninterrupted service and should merge. The predicate must accommodate the interval type: for closed intervals (inclusive start and end), use start_date > previous_end_date + INTERVAL '1' DAY. For half-open intervals [start, end) (where end is exclusive), the condition start_date > previous_end_date naturally merges adjacents because January 11 equals January 11. ANSI SQL supports interval arithmetic directly, so the solution requires CASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 END.

Why does the MAX(end_date) window function produce incorrect island boundaries when the input contains NULL values representing indefinite coverage?

ANSI SQL aggregate window functions like MAX() ignore NULL values in the frame. If a policy has no end date (NULL signifying “current”), MAX(end_date) over preceding rows returns the latest non-NULL date, potentially merging subsequent intervals that should start a new island after an indefinite gap. Candidates must recognize that NULLs require special treatment: either filter them out in a preliminary CTE, or use COALESCE(end_date, DATE '9999-12-31') to treat indefinite coverage as extending to infinity. Alternatively, treat NULL as a forced break by using CASE WHEN end_date IS NULL THEN 0 ELSE 1 END logic, ensuring the next row starts a new island.

How would you extend this logic to multi-dimensional packing, such as consolidating intervals separately for each combination of patient_id and insurance_type without losing atomicity?

Many candidates attempt subqueries or self-joins partitioned manually. The correct approach leverages the PARTITION BY clause in ANSI SQL window functions. Modify the frame specification to PARTITION BY patient_id, insurance_type in both the MAX(end_date) and SUM(is_new_island) calculations. This ensures the running maximum and island ID counter reset for each distinct group, maintaining O(n log n) performance across partitions. Failure to partition correctly causes “bleed-over” where a gap in one patient’s timeline incorrectly triggers a new island for another patient, corrupting the consolidation logic.