Apple: Apple Music Family Plans With Spare Seats

Easy
604.

Apple: Apple Music Family Plans With Spare Seats

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

ColumnTypeValues
group_idint
organizer_namevarchar
planenumindividual, student, family

Primary key: group_id.

One row per subscription; the organiser pays for it.

Table: PlanMember

ColumnType
group_idint
member_namevarchar

Primary key (together): group_id, member_name.

One row per person invited into a family plan — never the organiser, never more than five a group.

Example

The rows must come back in the order shown.

MusicPlan

group_idorganizer_nameplan
1Aaravfamily
2Diyaindividual
3Kabirfamily
4Meerafamily
5Rohanstudent
6Zarafamily

PlanMember

group_idmember_name
1Neha
1Vikram
3Priya
3Rahul
3Riya
3Sneha
3Tanvi
4Arjun
4Kavya
4Ira

Output

group_idorganizer_namespare_seats
6Zara5
1Aarav3
4Meera2
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.

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

ColumnType
group_idint
organizer_namevarchar
planenum(individual, student, family)

Primary key: group_id.

One row per subscription; the organiser pays for it.

Table: PlanMember

ColumnType
group_idint
member_namevarchar

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_idorganizer_nameplan
1Aaravfamily
2Diyaindividual
3Kabirfamily
4Meerafamily
5Rohanstudent
6Zarafamily

PlanMember

group_idmember_name
1Neha
1Vikram
3Priya
3Rahul
3Riya
3Sneha
3Tanvi
4Arjun
4Kavya
4Ira

Output

group_idorganizer_namespare_seats
6Zara5
1Aarav3
4Meera2

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_id

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