Spotify: Collaborative Playlists Only the Owner Edits

Medium
934.

Spotify: Collaborative Playlists Only the Owner Edits

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

ColumnType
playlist_idint
playlist_namevarchar
owner_idint
is_collaborativebool

Primary key: playlist_id.

One row per user playlist; owner_id is the user who created it.

Table: PlaylistAdd

ColumnType
playlist_idint
track_idint
added_byint
added_atdatetime

Primary key (together): playlist_id, track_id.

One row per track currently on a playlist, with the user who added it.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Playlist

playlist_idplaylist_nameowner_idis_collaborative
1Goa Trip 20241001
2Hostel Room 2042001
3Late Night Drive3000
4Office Friday4001
5Wedding Sangeet5001

PlaylistAdd

playlist_idtrack_idadded_byadded_at
1111002024-04-01 10:00:00
1121002024-04-01 10:02:00
1131002024-04-02 18:30:00
2112002024-05-10 22:00:00
2142012024-05-10 22:05:00
3153002024-06-01 01:00:00
5165002024-07-07 12:00:00

Output

playlist_idplaylist_nametracks_added
1Goa Trip 20243
5Wedding Sangeet1
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: 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

ColumnType
playlist_idint
playlist_namevarchar
owner_idint
is_collaborativebool

Primary key: playlist_id.

One row per user playlist; owner_id is the user who created it.

Table: PlaylistAdd

ColumnType
playlist_idint
track_idint
added_byint
added_atdatetime

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_idplaylist_nameowner_idis_collaborative
1Goa Trip 20241001
2Hostel Room 2042001
3Late Night Drive3000
4Office Friday4001
5Wedding Sangeet5001

PlaylistAdd

playlist_idtrack_idadded_byadded_at
1111002024-04-01 10:00:00
1121002024-04-01 10:02:00
1131002024-04-02 18:30:00
2112002024-05-10 22:00:00
2142012024-05-10 22:05:00
3153002024-06-01 01:00:00
5165002024-07-07 12:00:00

Output

playlist_idplaylist_nametracks_added
1Goa Trip 20243
5Wedding Sangeet1

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) = 0

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