Merge Overlapping Subscription Periods

Write a query to merge rows in subscriptions into continuous coverage spans for each customer.

A period joins the current span when its starts_on is on or before the day after the latest covered ends_on so far. Merge overlaps, nested periods and next-day renewals. A start after an uncovered day begins a new span.

Return coverage_start, coverage_end and periods, the number of original subscription rows absorbed into the span. Nested periods count as source rows even when they do not extend coverage.

Ordering: Sort by customer, then coverage_start in ascending order.

Returncustomercoverage_startcoverage_endperiods

Columns

subscriptions4 columns

columntype

sub_idBIGINT

customerVARCHAR

starts_onDATE

ends_onDATE

Sample data & expected output

8 rows · 4 columns

sub_idcustomerstarts_onends_on

1c_0012026-01-012026-02-10

2c_0012026-01-102026-01-15

3c_0012026-02-012026-02-05

4c_0012026-03-052026-03-10

5c_0022026-01-012026-01-05

6c_0022026-01-062026-01-09

7c_0032026-05-012026-05-31

8c_0022026-02-052026-03-20

If you get stuck

Show hint · 3 hints Walkthrough Reveal answer

About this problem

You practise islands where each row is an interval rather than a point, which is what subscription coverage, hotel bookings and resource reservations all look like. For each customer you merge overlapping and adjacent periods into continuous spans and report how many rows each span absorbed. The skill is knowing what the current row must be compared against, which is not the previous row.

Every other problem in this pattern compares a row with its neighbour, and that habit is exactly what fails here. Once periods can contain one another, the previous row by start date tells you nothing about how far coverage reaches: period 2 is a six-day add-on inside a six-week subscription, and comparing against it declares a gap that does not exist. The rest is boundary care. Adjacency is a decision – is the 6th of January continuous with coverage that ended on the 5th? – and the prompt says yes. Getting either wrong splits a span, and a split span looks like an extra row of real data rather than an error. The last trap is coverage_end: taking the last row’s end instead of the maximum is right whenever periods are tidy and wrong exactly when they are not, which is when this query is needed.

Read the lesson: Gaps, Islands & Streaks (P19) →

Your query · runs in your browser⌘ Enter to run

Loading editor…

Output · run to see rows