Spotify: Skips and Full Plays per Track — SQL Problem
An editor deciding which songs stay on a flagship playlist looks at two signals per track: how often listeners bail out early and how often they hear it to…
- Difficulty: Easy
- Topics: Aggregation, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #925
Problem statement
An editor deciding which songs stay on a flagship playlist looks at two signals per track: how often listeners bail out early and how often they hear it to the end.
A play is a skip when ms_played is below 30000 (under 30 seconds), and a full play when ms_played is at least the track's duration_ms; a play can be neither. For every track with at least one play, return track_id, title, skips and full_plays. 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: Track
| Column | Type |
|---|---|
| track_id | int |
| title | varchar |
| duration_ms | int |
Primary key: track_id.
duration_ms is the track's length in milliseconds; every track is longer than 30 seconds.
Table: Play
| Column | Type |
|---|---|
| play_id | int |
| track_id | int |
| listener_id | int |
| ms_played | int |
Primary key: play_id.
One row per play; ms_played is how long it was heard, never more than the track's length.
Examples
Example 1
Track
| track_id | title | duration_ms |
|---|---|---|
| 1 | Monsoon Letters | 200000 |
| 2 | Chasing Neon | 185000 |
| 3 | Paper Planes | 240000 |
| 4 | Golden Hour | 210000 |
Play
| play_id | track_id | listener_id | ms_played |
|---|---|---|---|
| 1 | 1 | 7 | 12000 |
| 2 | 1 | 8 | 200000 |
| 3 | 1 | 9 | 30000 |
| 4 | 2 | 7 | 29999 |
| 5 | 2 | 8 | 4000 |
| 6 | 3 | 9 | 240000 |
| 7 | 3 | 7 | 240000 |
| 8 | 3 | 8 | 150000 |
Output
| track_id | title | skips | full_plays |
|---|---|---|---|
| 1 | Monsoon Letters | 1 | 1 |
| 2 | Chasing Neon | 2 | 0 |
| 3 | Paper Planes | 0 | 2 |
How to solve Spotify: Skips and Full Plays per Track
Two different counts over the same group is the textbook case for conditional aggregation. Join each play to its track (the full-play test needs duration_ms), group by track, and inside the group add 1 for each row that meets a condition: SUM(CASE WHEN ms_played < 30000 THEN 1 ELSE 0 END) counts skips, and the same shape with ms_played >= duration_ms counts full plays. A play of 2 minutes on a 3-minute song adds to neither, which is what the statement asks.
Both boundaries are on purpose: 29,999 ms is a skip and 30,000 ms is not, and a play that reaches the last millisecond is a full play. The inner join leaves out tracks with no plays at all, and a track whose plays were all middling still appears, with two zeros — ELSE 0 keeps the sums at 0 rather than NULL.
COUNT(IF(cond, 1, NULL)) is the same idea, since COUNT ignores NULL. The correlated-subquery version computes each count separately per track; it reads the plays twice and is the slowest of the three, while the grouped join is a single pass.
Reference solution (MySQL)
SELECT t.track_id, t.title,
SUM(CASE WHEN p.ms_played < 30000 THEN 1 ELSE 0 END) AS skips,
SUM(CASE WHEN p.ms_played >= t.duration_ms THEN 1 ELSE 0 END) AS full_plays
FROM Track t
JOIN Play p ON p.track_id = t.track_id
GROUP BY t.track_id, t.titleAnother way
SELECT t.track_id, t.title, COUNT(IF(p.ms_played < 30000, 1, NULL)) AS skips, COUNT(IF(p.ms_played >= t.duration_ms, 1, NULL)) AS full_plays FROM Track t JOIN Play p ON p.track_id = t.track_id GROUP BY t.track_id, t.titleAnother way
SELECT t.track_id, t.title, (SELECT COUNT(*) FROM Play p WHERE p.track_id = t.track_id AND p.ms_played < 30000) AS skips, (SELECT COUNT(*) FROM Play p WHERE p.track_id = t.track_id AND p.ms_played >= t.duration_ms) AS full_plays FROM Track t WHERE EXISTS (SELECT 1 FROM Play p WHERE p.track_id = t.track_id)← Spotify: Long-Form Podcast Episodes Released in 2024 · Spotify: Family and Duo Plans With Open Seats →