LinkedIn: Merge Overlapping Premium Periods Into Spans
Members switch Premium plans, renew early and come back after a lapse, so their subscription rows overlap.
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #744
Problem statement
Members switch Premium plans, renew early and come back after a lapse, so their subscription rows overlap. The loyalty programme needs each member's continuous Premium spans.
Merge a member's periods into spans: two periods belong to the same span when they overlap or touch — a period starting on the day after another ends continues it, while a gap of one or more whole days without access starts a new span. Both start_date and end_date are days of access. Return member_id, span_start, span_end and premium_days (days of access in the span, both ends included). Order the rows by member_id, then span_start.
A practice scenario set at LinkedIn: the tables and data are invented, and this is not a question from, or endorsed by, LinkedIn.
Tables
Table: PremiumSubscription
| Column | Type |
|---|---|
| subscription_id | int |
| member_id | int |
| plan | varchar |
| start_date | date |
| end_date | date |
Primary key: subscription_id.
One row per subscription period; access runs from start_date to end_date inclusive, and end_date is never before start_date.
Examples
Example 1
PremiumSubscription
| subscription_id | member_id | plan | start_date | end_date |
|---|---|---|---|---|
| 1 | 7 | Career | 2025-01-01 | 2025-01-31 |
| 2 | 7 | Business | 2025-01-20 | 2025-02-28 |
| 3 | 7 | Business | 2025-03-01 | 2025-03-31 |
| 4 | 7 | Career | 2025-04-02 | 2025-04-30 |
| 5 | 8 | Sales Navigator | 2025-02-01 | 2025-06-30 |
| 6 | 8 | Career | 2025-03-01 | 2025-03-15 |
| 7 | 8 | Career | 2025-07-01 | 2025-07-31 |
| 8 | 9 | Recruiter Lite | 2025-05-10 | 2025-05-10 |
Output
| member_id | span_start | span_end | premium_days |
|---|---|---|---|
| 7 | 2025-01-01 | 2025-03-31 | 90 |
| 7 | 2025-04-02 | 2025-04-30 | 29 |
| 8 | 2025-02-01 | 2025-07-31 | 181 |
| 9 | 2025-05-10 | 2025-05-10 | 1 |
How to solve LinkedIn: Merge Overlapping Premium Periods Into Spans
Interval merging is gaps and islands where the islands are defined by coverage rather than by consecutive numbers. Sort a member's periods by start date. A period continues the current span if it starts no later than the day after the furthest end reached by any earlier period — MAX(end_date) over the rows before it, not LAG(end_date): in the example, member 8's long Sales Navigator period covers the short March period, and the July period must be compared with June 30, not with March 15. The window ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING gives exactly that running reach (NULL for the first period, which always opens a span).
"Touching" counts as continuous, so the test is start_date > DATE_ADD(reach, INTERVAL 1 DAY): a period starting on March 1 after one ending on February 28 merges, one starting on April 2 after March 31 leaves April 1 uncovered and opens a new span. A running SUM of the flags numbers the spans, and grouping by it gives each span's first start and last end; the day count adds 1 because both ends are days of access. Periods with equal starts are ordered by id so the reach is well defined.
The alternative finds span boundaries directly. A start opens a span when no earlier-starting period reaches its previous day; an end closes one when no period ending later starts by the next day. Each span is then a start paired with the first closing end at or after it. It is quadratic, while the window version is one sort per member.
Reference solution (MySQL)
WITH ordered AS (
SELECT subscription_id, member_id, start_date, end_date,
MAX(end_date) OVER (PARTITION BY member_id ORDER BY start_date, subscription_id
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS reach
FROM PremiumSubscription
), flagged AS (
SELECT subscription_id, member_id, start_date, end_date,
CASE WHEN reach IS NULL OR start_date > DATE_ADD(reach, INTERVAL 1 DAY) THEN 1 ELSE 0 END AS new_span
FROM ordered
), numbered AS (
SELECT subscription_id, member_id, start_date, end_date,
SUM(new_span) OVER (PARTITION BY member_id ORDER BY start_date, subscription_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS span_no
FROM flagged
)
SELECT member_id, MIN(start_date) AS span_start, MAX(end_date) AS span_end,
DATEDIFF(MAX(end_date), MIN(start_date)) + 1 AS premium_days
FROM numbered
GROUP BY member_id, span_no
ORDER BY member_id, span_startAnother way
WITH starts AS (
SELECT DISTINCT s.member_id, s.start_date FROM PremiumSubscription s
WHERE NOT EXISTS (SELECT 1 FROM PremiumSubscription o WHERE o.member_id = s.member_id
AND o.start_date < s.start_date AND o.end_date >= DATE_SUB(s.start_date, INTERVAL 1 DAY))
), ends AS (
SELECT DISTINCT s.member_id, s.end_date FROM PremiumSubscription s
WHERE NOT EXISTS (SELECT 1 FROM PremiumSubscription o WHERE o.member_id = s.member_id
AND o.end_date > s.end_date AND o.start_date <= DATE_ADD(s.end_date, INTERVAL 1 DAY))
)
SELECT st.member_id, st.start_date AS span_start, MIN(en.end_date) AS span_end,
DATEDIFF(MIN(en.end_date), st.start_date) + 1 AS premium_days
FROM starts st JOIN ends en ON en.member_id = st.member_id AND en.end_date >= st.start_date
GROUP BY st.member_id, st.start_date
ORDER BY st.member_id, span_start← LinkedIn: Applications Year over Year by Company and Quarter · LinkedIn: First- and Second-Degree Network Size →