Spotify: Listeners Who Played an Album Front to Back

Medium
939.

Spotify: Listeners Who Played an Album Front to Back

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

ColumnType
album_idint
album_titlevarchar
artist_namevarchar

Primary key: album_id.

One row per album; titles are unique.

Table: AlbumTrack

ColumnType
track_idint
album_idint
track_noint

Primary key: track_id.

Every track belongs to exactly one album, and every album has at least one track.

Table: Play

ColumnType
play_idint
listener_idint
track_idint
ms_playedint

Primary key: play_id.

One row per play of a track.

Example

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

Album

album_idalbum_titleartist_name
1Monsoon LettersKavya Rao
2Neon BazaarCoral Static
3Single OriginEcho Harbour

AlbumTrack

track_idalbum_idtrack_no
1111
1212
1313
2121
2222
3131

Play

play_idlistener_idtrack_idms_played
1713200000
271131000
371230000
47121000
5811210000
6812190000
781329999
8821200000
9822200000
10821200000
2 more rows

Output

listener_idalbum_title
7Monsoon Letters
8Neon Bazaar
9Single Origin
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: 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

ColumnType
album_idint
album_titlevarchar
artist_namevarchar

Primary key: album_id.

One row per album; titles are unique.

Table: AlbumTrack

ColumnType
track_idint
album_idint
track_noint

Primary key: track_id.

Every track belongs to exactly one album, and every album has at least one track.

Table: Play

ColumnType
play_idint
listener_idint
track_idint
ms_playedint

Primary key: play_id.

One row per play of a track.

Examples

Example 1

Album

album_idalbum_titleartist_name
1Monsoon LettersKavya Rao
2Neon BazaarCoral Static
3Single OriginEcho Harbour

AlbumTrack

track_idalbum_idtrack_no
1111
1212
1313
2121
2222
3131

Play

play_idlistener_idtrack_idms_played
1713200000
271131000
371230000
47121000
5811210000
6812190000
781329999
8821200000
9822200000
10821200000
1193145000
1292160000

Output

listener_idalbum_title
7Monsoon Letters
8Neon Bazaar
9Single 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 →