Spotify: Family Plans That Ever Had Over Six Members at Once

Hard
943.

Spotify: Family Plans That Ever Had Over Six Members at Once

A Premium Family plan has six seats, but a billing bug let some plans add members while full. To size the fix, the subscriptions team replays membership history. A membership counts from joined_on up to but not including left_on; a NULL left_on means the person is still on the plan. Someone who leaves and a new member who joins on the same day never overlap.

Return every plan whose number of simultaneous members was ever more than 6, with the columns plan_id, peak_members (the largest number of members on any single day) and first_day_over (the first date on which the plan had more than 6 members). Order by plan_id.

A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.

Tables

Table: FamilyMembership

ColumnType
membership_idint
plan_idint
user_idint
joined_ondate
left_ondate

Primary key: membership_id.

One row per stint of a user on a Family plan; left_on is after joined_on, or NULL while the stint is ongoing. A user can have several stints.

Example

The rows must come back in the order shown.

FamilyMembership

membership_idplan_iduser_idjoined_onleft_on
11112024-01-01NULL
21122024-01-01NULL
31132024-01-05NULL
41142024-01-102024-03-01
51152024-02-01NULL
61162024-02-10NULL
71172024-03-01NULL
81182024-03-012024-03-20
92212024-01-01NULL
102222024-01-01NULL
5 more rows

Output

plan_idpeak_membersfirst_day_over
172024-03-01
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.

Spotify: Family Plans That Ever Had Over Six Members at Once

A Premium Family plan has six seats, but a billing bug let some plans add members while full.

  • Difficulty: Hard
  • Topics: Window Functions, Dates, Subqueries
  • Dialect: MySQL
  • Problem: #943

Problem statement

A Premium Family plan has six seats, but a billing bug let some plans add members while full. To size the fix, the subscriptions team replays membership history. A membership counts from joined_on up to but not including left_on; a NULL left_on means the person is still on the plan. Someone who leaves and a new member who joins on the same day never overlap.

Return every plan whose number of simultaneous members was ever more than 6, with the columns plan_id, peak_members (the largest number of members on any single day) and first_day_over (the first date on which the plan had more than 6 members). Order by plan_id.

A practice scenario set at Spotify: the tables and data are invented, and this is not a question from, or endorsed by, Spotify.

Tables

Table: FamilyMembership

ColumnType
membership_idint
plan_idint
user_idint
joined_ondate
left_ondate

Primary key: membership_id.

One row per stint of a user on a Family plan; left_on is after joined_on, or NULL while the stint is ongoing. A user can have several stints.

Examples

Example 1

FamilyMembership

membership_idplan_iduser_idjoined_onleft_on
11112024-01-01NULL
21122024-01-01NULL
31132024-01-05NULL
41142024-01-102024-03-01
51152024-02-01NULL
61162024-02-10NULL
71172024-03-01NULL
81182024-03-012024-03-20
92212024-01-01NULL
102222024-01-01NULL
112232024-01-012024-02-01
122242024-01-15NULL
132252024-01-20NULL
142262024-01-25NULL
152272024-02-01NULL

Output

plan_idpeak_membersfirst_day_over
172024-03-01

How to solve Spotify: Family Plans That Ever Had Over Six Members at Once

Counting overlapping intervals is a sweep line. Each membership becomes a +1 event on its join date and, if it ended, a −1 event on its leave date. Sorted by date, the running sum of the events is the number of members from that day until the next event, so the peak is the maximum running sum and the first day over six is the earliest date whose running sum exceeds 6.

Same-day events need care. The statement makes left_on exclusive, so a member leaving on 1 March and another joining on 1 March are never on the plan together. Netting the deltas per (plan_id, day) before the running total implements exactly that — plan 2's member 23 leaves on 1 February as member 27 joins, and the count stays at 6, so plan 2 never goes over. Without the netting a window ordered by date alone would process the two events in an arbitrary order and could report a phantom 7. Plan 1 reaches six on 10 February, and on 1 March two people join while one leaves, taking it to 7 — its first day over.

The alternative uses the observation that the head count rises only on join days: for every join date it counts the stints active on that day with a correlated subquery. That is quadratic per plan, while the sweep is one sort of the events.

Reference solution (MySQL)

WITH events AS (
  SELECT plan_id, joined_on AS d, 1 AS delta FROM FamilyMembership
  UNION ALL
  SELECT plan_id, left_on AS d, -1 AS delta FROM FamilyMembership WHERE left_on IS NOT NULL
), daily AS (
  SELECT plan_id, d, SUM(delta) AS delta
  FROM events
  GROUP BY plan_id, d
), running AS (
  SELECT plan_id, d, SUM(delta) OVER (PARTITION BY plan_id ORDER BY d) AS members
  FROM daily
)
SELECT plan_id, MAX(members) AS peak_members,
       MIN(CASE WHEN members > 6 THEN d END) AS first_day_over
FROM running
GROUP BY plan_id
HAVING MAX(members) > 6
ORDER BY plan_id

Another way

SELECT plan_id, MAX(c) AS peak_members, MIN(CASE WHEN c > 6 THEN joined_on END) AS first_day_over
FROM (SELECT f.plan_id, f.joined_on,
             (SELECT COUNT(*) FROM FamilyMembership g
              WHERE g.plan_id = f.plan_id AND g.joined_on <= f.joined_on
                AND (g.left_on IS NULL OR g.left_on > f.joined_on)) AS c
      FROM FamilyMembership f) x
GROUP BY plan_id
HAVING MAX(c) > 6
ORDER BY plan_id

← Spotify: Podcast Episode-to-Episode Listener Retention · Spotify: Weekly Top 3 Chart With New, Re-Entry, Up and Down Labels →