Spotify: Listeners Who Played an Album Front to Back
Album-first listening is something labels ask about when deciding on track order.
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Joins
- Dialect: MySQL
- Problem: #939
Problem statement
Album-first listening is something labels ask about when deciding on track order. A listener completed an album when they played every track on it at least once for 30 seconds or more (ms_played of 30000 or more); the plays can be in any order and on any days, and shorter plays do not count.
Return one row per completed album and listener, with the columns listener_id and album_title. 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: Album
| Column | Type |
|---|---|
| album_id | int |
| album_title | varchar |
| artist_name | varchar |
Primary key: album_id.
One row per album; titles are unique.
Table: AlbumTrack
| Column | Type |
|---|---|
| track_id | int |
| album_id | int |
| track_no | int |
Primary key: track_id.
Every track belongs to exactly one album, and every album has at least one track.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| listener_id | int |
| track_id | int |
| ms_played | int |
Primary key: play_id.
One row per play of a track.
Examples
Example 1
Album
| album_id | album_title | artist_name |
|---|---|---|
| 1 | Monsoon Letters | Kavya Rao |
| 2 | Neon Bazaar | Coral Static |
| 3 | Single Origin | Echo Harbour |
AlbumTrack
| track_id | album_id | track_no |
|---|---|---|
| 11 | 1 | 1 |
| 12 | 1 | 2 |
| 13 | 1 | 3 |
| 21 | 2 | 1 |
| 22 | 2 | 2 |
| 31 | 3 | 1 |
Play
| play_id | listener_id | track_id | ms_played |
|---|---|---|---|
| 1 | 7 | 13 | 200000 |
| 2 | 7 | 11 | 31000 |
| 3 | 7 | 12 | 30000 |
| 4 | 7 | 12 | 1000 |
| 5 | 8 | 11 | 210000 |
| 6 | 8 | 12 | 190000 |
| 7 | 8 | 13 | 29999 |
| 8 | 8 | 21 | 200000 |
| 9 | 8 | 22 | 200000 |
| 10 | 8 | 21 | 200000 |
| 11 | 9 | 31 | 45000 |
| 12 | 9 | 21 | 60000 |
Output
| listener_id | album_title |
|---|---|
| 7 | Monsoon Letters |
| 8 | Neon Bazaar |
| 9 | Single Origin |
How to solve Spotify: Listeners Who Played an Album Front to Back
"Every track on the album" makes this relational division. The counting form is the most common: keep plays of at least 30 seconds, group by listener and album, and count the distinct tracks played. The listener completed the album when that count equals the album's number of tracks.
Both details matter. Without DISTINCT, listener 8's two plays of track 21 would make the two-track album look complete after one track — here it really is complete, but the count must not depend on replays. And the 30-second filter must come before counting: listener 8 played Monsoon Letters' third track for only 29,999 ms, so that album is not complete, while listener 7's plays of exactly 30,000 ms do count. The one-track album completes with a single qualifying play.
The double NOT EXISTS alternative states the definition literally: there is no track on the album that the listener did not play for 30 seconds. It relies on every album having a track; on an empty album it would be vacuously true, while the counting form would never return it. The counting form is two aggregations; the nested anti-join probes plays per (listener, album, track) and wants an index on (listener_id, track_id).
Reference solution (MySQL)
SELECT q.listener_id, al.album_title
FROM (
SELECT p.listener_id, t.album_id, COUNT(DISTINCT p.track_id) AS played
FROM Play p
JOIN AlbumTrack t ON t.track_id = p.track_id
WHERE p.ms_played >= 30000
GROUP BY p.listener_id, t.album_id
) q
JOIN Album al ON al.album_id = q.album_id
WHERE q.played = (SELECT COUNT(*) FROM AlbumTrack t2 WHERE t2.album_id = q.album_id)Another way
SELECT DISTINCT p.listener_id, al.album_title
FROM Play p
JOIN AlbumTrack t ON t.track_id = p.track_id
JOIN Album al ON al.album_id = t.album_id
WHERE NOT EXISTS (
SELECT 1 FROM AlbumTrack t2
WHERE t2.album_id = t.album_id
AND NOT EXISTS (SELECT 1 FROM Play p2 WHERE p2.listener_id = p.listener_id AND p2.track_id = t2.track_id AND p2.ms_played >= 30000)
)Another way
WITH sizes AS (SELECT album_id, COUNT(*) AS n FROM AlbumTrack GROUP BY album_id)
SELECT p.listener_id, al.album_title
FROM Play p JOIN AlbumTrack t ON t.track_id = p.track_id JOIN Album al ON al.album_id = t.album_id JOIN sizes s ON s.album_id = t.album_id
WHERE p.ms_played >= 30000
GROUP BY p.listener_id, al.album_id, al.album_title, s.n
HAVING COUNT(DISTINCT p.track_id) = s.n← Spotify: Songwriter Splits That Don't Add Up to 100% · Spotify: Featured-Artist Credits in Track Titles per Label →