Spotify: Family and Duo Plans With Open Seats

Easy
926.

Spotify: Family and Duo Plans With Open Seats

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

ColumnTypeValues
plan_idint
plan_typeenumfamily, duo
started_ondate

Primary key: plan_id.

One row per shared Premium plan.

Table: PlanMember

ColumnType
plan_idint
user_idint
joined_ondate

Primary key (together): plan_id, user_id.

One row per person currently on a plan; a plan never has more members than seats.

Example

The rows must come back in the order shown.

PremiumPlan

plan_idplan_typestarted_on
1family2023-04-01
2duo2023-09-12
3family2024-01-20
4duo2024-02-02
5family2024-03-15

PlanMember

plan_iduser_idjoined_on
11012023-04-01
11022023-04-03
11032023-05-10
11042023-06-01
11052023-06-01
11062023-08-19
22012023-09-12
22022023-09-14
33012024-01-20
33022024-02-01
1 more row

Output

plan_idplan_typemembersopen_seats
5family06
3family24
4duo11
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 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

ColumnType
plan_idint
plan_typeenum(family, duo)
started_ondate

Primary key: plan_id.

One row per shared Premium plan.

Table: PlanMember

ColumnType
plan_idint
user_idint
joined_ondate

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_idplan_typestarted_on
1family2023-04-01
2duo2023-09-12
3family2024-01-20
4duo2024-02-02
5family2024-03-15

PlanMember

plan_iduser_idjoined_on
11012023-04-01
11022023-04-03
11032023-05-10
11042023-06-01
11052023-06-01
11062023-08-19
22012023-09-12
22022023-09-14
33012024-01-20
33022024-02-01
44012024-02-02

Output

plan_idplan_typemembersopen_seats
5family06
3family24
4duo11

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_id

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