Spotify: Skips and Full Plays per Track

Easy
925.

Spotify: Skips and Full Plays per Track

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

ColumnType
track_idint
titlevarchar
duration_msint

Primary key: track_id.

duration_ms is the track's length in milliseconds; every track is longer than 30 seconds.

Table: Play

ColumnType
play_idint
track_idint
listener_idint
ms_playedint

Primary key: play_id.

One row per play; ms_played is how long it was heard, never more than the track's length.

Example

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

Track

track_idtitleduration_ms
1Monsoon Letters200000
2Chasing Neon185000
3Paper Planes240000
4Golden Hour210000

Play

play_idtrack_idlistener_idms_played
11712000
218200000
31930000
42729999
5284000
639240000
737240000
838150000

Output

track_idtitleskipsfull_plays
1Monsoon Letters11
2Chasing Neon20
3Paper Planes02
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: 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

ColumnType
track_idint
titlevarchar
duration_msint

Primary key: track_id.

duration_ms is the track's length in milliseconds; every track is longer than 30 seconds.

Table: Play

ColumnType
play_idint
track_idint
listener_idint
ms_playedint

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_idtitleduration_ms
1Monsoon Letters200000
2Chasing Neon185000
3Paper Planes240000
4Golden Hour210000

Play

play_idtrack_idlistener_idms_played
11712000
218200000
31930000
42729999
5284000
639240000
737240000
838150000

Output

track_idtitleskipsfull_plays
1Monsoon Letters11
2Chasing Neon20
3Paper Planes02

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.title

Another 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.title

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