Spotify: Family and Duo Plans With Open Seats — SQL Problem
The Premium growth team nudges plan managers who are paying for seats nobody uses.
- Difficulty: Easy
- Topics: Aggregation, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #926
Problem statement
The Premium growth team nudges plan managers who are paying for seats nobody uses. A family plan has 6 seats and a duo plan has 2; every member, the manager included, takes one seat.
Return every plan with at least one open seat, with the columns plan_id, plan_type, members and open_seats. A plan with no member rows at all has every seat open. Order by open_seats from most to fewest, then 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: PremiumPlan
| Column | Type |
|---|---|
| plan_id | int |
| plan_type | enum(family, duo) |
| started_on | date |
Primary key: plan_id.
One row per shared Premium plan.
Table: PlanMember
| Column | Type |
|---|---|
| plan_id | int |
| user_id | int |
| joined_on | date |
Primary key: plan_id, user_id.
One row per person currently on a plan; a plan never has more members than seats.
Examples
Example 1
PremiumPlan
| plan_id | plan_type | started_on |
|---|---|---|
| 1 | family | 2023-04-01 |
| 2 | duo | 2023-09-12 |
| 3 | family | 2024-01-20 |
| 4 | duo | 2024-02-02 |
| 5 | family | 2024-03-15 |
PlanMember
| plan_id | user_id | joined_on |
|---|---|---|
| 1 | 101 | 2023-04-01 |
| 1 | 102 | 2023-04-03 |
| 1 | 103 | 2023-05-10 |
| 1 | 104 | 2023-06-01 |
| 1 | 105 | 2023-06-01 |
| 1 | 106 | 2023-08-19 |
| 2 | 201 | 2023-09-12 |
| 2 | 202 | 2023-09-14 |
| 3 | 301 | 2024-01-20 |
| 3 | 302 | 2024-02-01 |
| 4 | 401 | 2024-02-02 |
Output
| plan_id | plan_type | members | open_seats |
|---|---|---|---|
| 5 | family | 0 | 6 |
| 3 | family | 2 | 4 |
| 4 | duo | 1 | 1 |
How to solve Spotify: Family and Duo Plans With Open Seats
Open seats are seats minus members. Seats come from the plan type — CASE WHEN plan_type = 'family' THEN 6 ELSE 2 END — and members are a count per plan, so the query groups the plan's member rows and subtracts.
The trap is the plan with nobody on it. An inner join would drop it, yet it is the plan with the most open seats. A LEFT JOIN keeps it as one row with NULL member columns, and COUNT(m.user_id) counts that NULL as 0 — COUNT(*) would have counted the row and reported one member. Full plans are removed in HAVING, since the condition is on an aggregate.
The order is fixed by the statement: most open seats first, plan_id breaking ties. The alternative computes each plan's member count with a correlated subquery and filters in an outer query, which reads naturally but probes the members table once per plan; the grouped LEFT JOIN does it in one pass.
Reference solution (MySQL)
SELECT p.plan_id, p.plan_type, COUNT(m.user_id) AS members,
CASE WHEN p.plan_type = 'family' THEN 6 ELSE 2 END - COUNT(m.user_id) AS open_seats
FROM PremiumPlan p
LEFT JOIN PlanMember m ON m.plan_id = p.plan_id
GROUP BY p.plan_id, p.plan_type
HAVING COUNT(m.user_id) < CASE WHEN p.plan_type = 'family' THEN 6 ELSE 2 END
ORDER BY open_seats DESC, p.plan_idAnother way
SELECT plan_id, plan_type, members, seats - members AS open_seats
FROM (SELECT p.plan_id, p.plan_type, IF(p.plan_type = 'family', 6, 2) AS seats,
(SELECT COUNT(*) FROM PlanMember m WHERE m.plan_id = p.plan_id) AS members
FROM PremiumPlan p) x
WHERE members < seats
ORDER BY open_seats DESC, plan_id← Spotify: Skips and Full Plays per Track · Spotify: Artists Who Released Nothing in 2024 →