Spotify: Collaborative Playlists Only the Owner Edits
A collaborative playlist is meant to be built by friends together.
- Difficulty: Medium
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #934
Problem statement
A collaborative playlist is meant to be built by friends together. The social features team wants the collaborative playlists where, so far, every track was added by the owner — the owner switched collaboration on but nobody joined in — to test an "invite a friend" nudge.
Return every collaborative playlist with at least one added track, all of them added by its owner, with the columns playlist_id, playlist_name and tracks_added. Playlists that are not collaborative, and collaborative playlists with no tracks yet, are not in the answer. Return the rows in any order.
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: Playlist
| Column | Type |
|---|---|
| playlist_id | int |
| playlist_name | varchar |
| owner_id | int |
| is_collaborative | bool |
Primary key: playlist_id.
One row per user playlist; owner_id is the user who created it.
Table: PlaylistAdd
| Column | Type |
|---|---|
| playlist_id | int |
| track_id | int |
| added_by | int |
| added_at | datetime |
Primary key: playlist_id, track_id.
One row per track currently on a playlist, with the user who added it.
Examples
Example 1
Playlist
| playlist_id | playlist_name | owner_id | is_collaborative |
|---|---|---|---|
| 1 | Goa Trip 2024 | 100 | 1 |
| 2 | Hostel Room 204 | 200 | 1 |
| 3 | Late Night Drive | 300 | 0 |
| 4 | Office Friday | 400 | 1 |
| 5 | Wedding Sangeet | 500 | 1 |
PlaylistAdd
| playlist_id | track_id | added_by | added_at |
|---|---|---|---|
| 1 | 11 | 100 | 2024-04-01 10:00:00 |
| 1 | 12 | 100 | 2024-04-01 10:02:00 |
| 1 | 13 | 100 | 2024-04-02 18:30:00 |
| 2 | 11 | 200 | 2024-05-10 22:00:00 |
| 2 | 14 | 201 | 2024-05-10 22:05:00 |
| 3 | 15 | 300 | 2024-06-01 01:00:00 |
| 5 | 16 | 500 | 2024-07-07 12:00:00 |
Output
| playlist_id | playlist_name | tracks_added |
|---|---|---|
| 1 | Goa Trip 2024 | 3 |
| 5 | Wedding Sangeet | 1 |
How to solve Spotify: Collaborative Playlists Only the Owner Edits
"All adds were by the owner" is a universal condition, and SQL has two standard ways to test one. The grouped way joins every add to its playlist, groups per playlist, and requires that the number of foreign adds is zero: HAVING SUM(CASE WHEN added_by <> owner_id THEN 1 ELSE 0 END) = 0. COUNT(*) in the same group is the number of tracks added.
The other way turns all into none: keep the playlist when NOT EXISTS an add by somebody else. On its own that is true for an empty playlist too — a vacuous "everyone who added was the owner" — so the alternative also demands EXISTS an add. The grouped join gets that for free, since a playlist with no adds forms no group. Non-collaborative playlists are filtered first; Late Night Drive has only the owner's tracks but is not collaborative.
The third query says the same with one distinct contributor who is the owner. The grouped forms read the adds once; the subquery form probes them up to three times per playlist, cheap with the (playlist_id, track_id) key.
Reference solution (MySQL)
SELECT p.playlist_id, p.playlist_name, COUNT(*) AS tracks_added
FROM Playlist p
JOIN PlaylistAdd a ON a.playlist_id = p.playlist_id
WHERE p.is_collaborative = 1
GROUP BY p.playlist_id, p.playlist_name, p.owner_id
HAVING SUM(CASE WHEN a.added_by <> p.owner_id THEN 1 ELSE 0 END) = 0Another way
SELECT p.playlist_id, p.playlist_name, (SELECT COUNT(*) FROM PlaylistAdd a WHERE a.playlist_id = p.playlist_id) AS tracks_added
FROM Playlist p
WHERE p.is_collaborative = 1
AND EXISTS (SELECT 1 FROM PlaylistAdd a WHERE a.playlist_id = p.playlist_id)
AND NOT EXISTS (SELECT 1 FROM PlaylistAdd a WHERE a.playlist_id = p.playlist_id AND a.added_by <> p.owner_id)Another way
SELECT p.playlist_id, p.playlist_name, COUNT(*) AS tracks_added FROM Playlist p JOIN PlaylistAdd a ON a.playlist_id = p.playlist_id WHERE p.is_collaborative <> 0 GROUP BY p.playlist_id, p.playlist_name HAVING COUNT(DISTINCT a.added_by) = 1 AND MIN(a.added_by) = MIN(p.owner_id)← Spotify: Wrapped Top Artist of Every Listener · Spotify: Ad Load by Market Against the Four-an-Hour Cap →