LinkedIn: Merge Overlapping Premium Periods Into Spans

Hard
744.

LinkedIn: Merge Overlapping Premium Periods Into Spans

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

ColumnType
subscription_idint
member_idint
planvarchar
start_datedate
end_datedate

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.

Example

The rows must come back in the order shown.

PremiumSubscription

subscription_idmember_idplanstart_dateend_date
17Career2025-01-012025-01-31
27Business2025-01-202025-02-28
37Business2025-03-012025-03-31
47Career2025-04-022025-04-30
58Sales Navigator2025-02-012025-06-30
68Career2025-03-012025-03-15
78Career2025-07-012025-07-31
89Recruiter Lite2025-05-102025-05-10

Output

member_idspan_startspan_endpremium_days
72025-01-012025-03-3190
72025-04-022025-04-3029
82025-02-012025-07-31181
92025-05-102025-05-101
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
subscription_idint
member_idint
planvarchar
start_datedate
end_datedate

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_idmember_idplanstart_dateend_date
17Career2025-01-012025-01-31
27Business2025-01-202025-02-28
37Business2025-03-012025-03-31
47Career2025-04-022025-04-30
58Sales Navigator2025-02-012025-06-30
68Career2025-03-012025-03-15
78Career2025-07-012025-07-31
89Recruiter Lite2025-05-102025-05-10

Output

member_idspan_startspan_endpremium_days
72025-01-012025-03-3190
72025-04-022025-04-3029
82025-02-012025-07-31181
92025-05-102025-05-101

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_start

Another 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 →