Pattern
Merging overlapping periods
Merge periods that overlap or touch into continuous ones, with a running maximum of the end dates.
Sort the periods by start. A period opens a new block when it starts after the latest end seen so far:
MAX(end) OVER (… ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING). A running SUM of those flags numbers the blocks,
then MIN(start) and MAX(end) per block. Comparing with the previous period only (LAG) fails when a long period
contains shorter ones.
WITH memberships(started_on, ended_on) AS (VALUES
(DATE '2025-02-01', DATE '2025-12-31'),
(DATE '2025-03-15', DATE '2025-04-15'),
(DATE '2025-05-01', DATE '2025-06-01'),
(DATE '2026-02-01', DATE '2026-03-01')),
flagged AS (
SELECT started_on, ended_on,
CASE WHEN started_on <= MAX(ended_on) OVER (ORDER BY started_on
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 0 ELSE 1 END AS starts_new
FROM memberships
),
numbered AS (SELECT *, SUM(starts_new) OVER (ORDER BY started_on) AS period FROM flagged)
SELECT MIN(started_on) AS period_start, MAX(ended_on) AS period_end
FROM numbered
GROUP BY period
ORDER BY period_start;
| period_start | period_end |
|---|---|
| 2025-02-01 | 2025-12-31 |
| 2026-02-01 | 2026-03-01 |
Practise:Merge the overlapping periods