Intermediate
Open
Pro
Turning Overlapping Subscription Periods Into Merged Active Ranges
You have a subscription_periods table logging every time a customer's
subscription was (re)activated, with a start and end date per row. Due
to how the billing system issues renewals, the same customer can have
overlapping or directly adjacent periods (e.g., a renewal issued a
few days before the previous period technically ends):
customer_id | period_start | period_end
101 | 2024-01-01 | 2024-01-31
101 | 2024-01-28 | 2024-02-28 -- overlaps the row above
101 | 2024-03-01 | 2024-03-31 -- adjacent, no gap
101 | 2024-05-01 | 2024-05-31 -- real gap in April
Finance wants one row per customer per actual continuous period of
active subscription — i.e., rows 1–3 above should merge into a single
2024-01-01 to 2024-03-31 range, and row 4 should stay separate
because of the April gap.
- Explain why a simple
MIN(period_start)/MAX(period_end)grouped by customer would give the wrong answer here, and why the date-based gaps-and-islands trick from the subject (subtracting a row number from an ordered date) doesn't directly apply either. - Design a window-function-based approach that correctly merges overlapping/adjacent periods into continuous ranges and separates genuine gaps, and write the query.
- How would you validate that your query is correct beyond checking it against this one example — what edge cases would you specifically construct test rows for?
Share this question