Spotify: Shared Plans With Members at Different Addresses
Premium Family and Premium Duo are meant for people who live together, so the plan-integrity team looks for shared plans whose members' accounts show more…
- Difficulty: Medium
- Topics: Aggregation, Joins
- Dialect: MySQL
- Problem: #937
Problem statement
Premium Family and Premium Duo are meant for people who live together, so the plan-integrity team looks for shared plans whose members' accounts show more than one postal code. A member who never entered a postal code (NULL) neither proves nor breaks the rule and is ignored in the comparison.
Return every duo or family plan whose members have two or more different known postal codes, with the columns plan_id, plan_type, members (all members, including those without a postal code) and postal_codes (the number of distinct known codes). 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: SubscriptionPlan
| Column | Type |
|---|---|
| plan_id | int |
| plan_type | enum(individual, student, duo, family) |
Primary key: plan_id.
One row per Premium subscription.
Table: PlanMember
| Column | Type |
|---|---|
| user_id | int |
| plan_id | int |
Primary key: user_id.
Which plan each user belongs to; a user is on at most one plan.
Table: Account
| Column | Type |
|---|---|
| user_id | int |
| user_name | varchar |
| postal_code | varchar |
Primary key: user_id.
One row per user; postal_code is NULL when the user never entered one.
Examples
Example 1
SubscriptionPlan
| plan_id | plan_type |
|---|---|
| 1 | family |
| 2 | duo |
| 3 | duo |
| 4 | individual |
| 5 | family |
PlanMember
| user_id | plan_id |
|---|---|
| 11 | 1 |
| 12 | 1 |
| 13 | 1 |
| 14 | 1 |
| 21 | 2 |
| 22 | 2 |
| 31 | 3 |
| 32 | 3 |
| 41 | 4 |
| 51 | 5 |
| 52 | 5 |
Account
| user_id | user_name | postal_code |
|---|---|---|
| 11 | Aarav | 560001 |
| 12 | Aditi | 560001 |
| 13 | Ishaan | 560034 |
| 14 | Ira | NULL |
| 21 | Kabir | 400050 |
| 22 | Karan | NULL |
| 31 | Meera | 110017 |
| 32 | Mia | 600028 |
| 41 | Neha | 411014 |
| 51 | Riya | 411014 |
| 52 | Rohan | 411014 |
Output
| plan_id | plan_type | members | postal_codes |
|---|---|---|---|
| 1 | family | 4 | 2 |
| 3 | duo | 2 | 2 |
How to solve Spotify: Shared Plans With Members at Different Addresses
Per plan we need two numbers, so it is a grouped query over plan → member → account. COUNT(*) counts every member row, and COUNT(DISTINCT a.postal_code) counts the different codes. Aggregate functions skip NULL, so a member with no code adds to members but not to postal_codes — the statement's rule exactly. The HAVING keeps plans with more than one distinct code, and WHERE removes individual and student plans before grouping.
In the example the family plan 1 has codes 560001 (twice), 560034 and NULL: two distinct known codes, four members — flagged. Duo 2 has one code and one NULL, which is not evidence of two homes. Family 5 shares one code and passes.
Two other formulations agree. MIN(code) <> MAX(code) is true exactly when at least two distinct non-NULL values exist, since MIN and MAX also skip NULLs. And the self-join EXISTS looks for any two members of the plan whose codes differ — a NULL comparison is never true, so it ignores missing codes too. The grouped version is one pass; the self-join compares pairs and is quadratic in plan size, which is fine for at most six members.
Reference solution (MySQL)
SELECT p.plan_id, p.plan_type, COUNT(*) AS members, COUNT(DISTINCT a.postal_code) AS postal_codes
FROM SubscriptionPlan p
JOIN PlanMember m ON m.plan_id = p.plan_id
JOIN Account a ON a.user_id = m.user_id
WHERE p.plan_type IN ('duo', 'family')
GROUP BY p.plan_id, p.plan_type
HAVING COUNT(DISTINCT a.postal_code) > 1
ORDER BY p.plan_idAnother way
SELECT p.plan_id, p.plan_type, COUNT(*) AS members, COUNT(DISTINCT a.postal_code) AS postal_codes
FROM SubscriptionPlan p JOIN PlanMember m ON m.plan_id = p.plan_id JOIN Account a ON a.user_id = m.user_id
WHERE p.plan_type = 'duo' OR p.plan_type = 'family'
GROUP BY p.plan_id, p.plan_type
HAVING MIN(a.postal_code) <> MAX(a.postal_code)
ORDER BY p.plan_idAnother way
SELECT p.plan_id, p.plan_type, (SELECT COUNT(*) FROM PlanMember m WHERE m.plan_id = p.plan_id) AS members,
(SELECT COUNT(DISTINCT a.postal_code) FROM PlanMember m JOIN Account a ON a.user_id = m.user_id WHERE m.plan_id = p.plan_id) AS postal_codes
FROM SubscriptionPlan p
WHERE p.plan_type IN ('duo', 'family')
AND EXISTS (SELECT 1 FROM PlanMember m1 JOIN Account a1 ON a1.user_id = m1.user_id
JOIN PlanMember m2 ON m2.plan_id = m1.plan_id JOIN Account a2 ON a2.user_id = m2.user_id
WHERE m1.plan_id = p.plan_id AND a1.postal_code <> a2.postal_code)
ORDER BY p.plan_id← Spotify: Artists Not Touring Their Top Listening City · Spotify: Songwriter Splits That Don't Add Up to 100% →