Spotify: Shared Plans With Members at Different Addresses

Medium
937.

Spotify: Shared Plans With Members at Different Addresses

MediumAggregationJoins

MySQL

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

ColumnTypeValues
plan_idint
plan_typeenumindividual, student, duo, family

Primary key: plan_id.

One row per Premium subscription.

Table: PlanMember

ColumnType
user_idint
plan_idint

Primary key: user_id.

Which plan each user belongs to; a user is on at most one plan.

Table: Account

ColumnType
user_idint
user_namevarchar
postal_codevarchar

Primary key: user_id.

One row per user; postal_code is NULL when the user never entered one.

Example

The rows must come back in the order shown.

SubscriptionPlan

plan_idplan_type
1family
2duo
3duo
4individual
5family

PlanMember

user_idplan_id
111
121
131
141
212
222
313
323
414
515
1 more row

Account

user_iduser_namepostal_code
11Aarav560001
12Aditi560001
13Ishaan560034
14IraNULL
21Kabir400050
22KaranNULL
31Meera110017
32Mia600028
41Neha411014
51Riya411014
1 more row

Output

plan_idplan_typememberspostal_codes
1family42
3duo22
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: 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

ColumnType
plan_idint
plan_typeenum(individual, student, duo, family)

Primary key: plan_id.

One row per Premium subscription.

Table: PlanMember

ColumnType
user_idint
plan_idint

Primary key: user_id.

Which plan each user belongs to; a user is on at most one plan.

Table: Account

ColumnType
user_idint
user_namevarchar
postal_codevarchar

Primary key: user_id.

One row per user; postal_code is NULL when the user never entered one.

Examples

Example 1

SubscriptionPlan

plan_idplan_type
1family
2duo
3duo
4individual
5family

PlanMember

user_idplan_id
111
121
131
141
212
222
313
323
414
515
525

Account

user_iduser_namepostal_code
11Aarav560001
12Aditi560001
13Ishaan560034
14IraNULL
21Kabir400050
22KaranNULL
31Meera110017
32Mia600028
41Neha411014
51Riya411014
52Rohan411014

Output

plan_idplan_typememberspostal_codes
1family42
3duo22

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_id

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

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