Apple: Apple Music Family Plans With Spare Seats
The Apple Music team nudges family organisers who still have room to invite people.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #604
Problem statement
The Apple Music team nudges family organisers who still have room to invite people. A family plan covers six people: the organiser plus up to five members; individual and student plans have no seats to share.
For every family plan with at least one free seat, return group_id, organizer_name and spare_seats (5 minus the members already in the group). A family plan with no members has 5 spare seats. Order the rows by spare_seats descending, then group_id.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: MusicPlan
| Column | Type |
|---|---|
| group_id | int |
| organizer_name | varchar |
| plan | enum(individual, student, family) |
Primary key: group_id.
One row per subscription; the organiser pays for it.
Table: PlanMember
| Column | Type |
|---|---|
| group_id | int |
| member_name | varchar |
Primary key: group_id, member_name.
One row per person invited into a family plan — never the organiser, never more than five a group.
Examples
Example 1
MusicPlan
| group_id | organizer_name | plan |
|---|---|---|
| 1 | Aarav | family |
| 2 | Diya | individual |
| 3 | Kabir | family |
| 4 | Meera | family |
| 5 | Rohan | student |
| 6 | Zara | family |
PlanMember
| group_id | member_name |
|---|---|
| 1 | Neha |
| 1 | Vikram |
| 3 | Priya |
| 3 | Rahul |
| 3 | Riya |
| 3 | Sneha |
| 3 | Tanvi |
| 4 | Arjun |
| 4 | Kavya |
| 4 | Ira |
Output
| group_id | organizer_name | spare_seats |
|---|---|---|
| 6 | Zara | 5 |
| 1 | Aarav | 3 |
| 4 | Meera | 2 |
How to solve Apple: Apple Music Family Plans With Spare Seats
Spare seats are 5 − members, so the core is a member count per family group. A LEFT JOIN from the plans keeps family groups that have nobody in them yet, and COUNT(m.member_name) counts only real members, giving 0 — and so 5 spare seats — for an empty group. Filtering plan = 'family' in WHERE is safe here because it tests the plan table, which is the preserved side of the join.
Full groups (five members) have nothing to offer, so HAVING COUNT(m.member_name) < 5 removes them after counting. The order is fixed: most spare seats first, then by group id.
A correlated subquery that counts members per family plan, wrapped in a derived table so the outer query can filter and sort on spare_seats, gives the same rows. Either way it is one pass over the members with an index on group_id.
Reference solution (MySQL)
SELECT p.group_id, p.organizer_name, 5 - COUNT(m.member_name) AS spare_seats
FROM MusicPlan p
LEFT JOIN PlanMember m ON m.group_id = p.group_id
WHERE p.plan = 'family'
GROUP BY p.group_id, p.organizer_name
HAVING COUNT(m.member_name) < 5
ORDER BY spare_seats DESC, p.group_idAnother way
SELECT group_id, organizer_name, spare_seats FROM (SELECT p.group_id, p.organizer_name, 5 - (SELECT COUNT(*) FROM PlanMember m WHERE m.group_id = p.group_id) AS spare_seats FROM MusicPlan p WHERE p.plan = 'family') t WHERE spare_seats > 0 ORDER BY spare_seats DESC, group_id← Apple: App Store Apps Not Updated Since Mid-2023 · Apple: Online Orders With Both an iPhone and AirPods →