Paths Subjects Questions Quizzes Pricing Search
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.

  1. 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.
  2. Design a window-function-based approach that correctly merges overlapping/adjacent periods into continuous ranges and separates genuine gaps, and write the query.
  3. 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

← Back to Advanced SQL: Window Functions & CTEs practice

We use cookies for product analytics to improve OmniAtlas. See our Privacy Policy.