Spotify: Discover Weekly Save Rate per Listener
The personalisation team judges each Discover Weekly drop by how many of its recommendations listeners save.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #931
Problem statement
The personalisation team judges each Discover Weekly drop by how many of its recommendations listeners save. A recommendation counts as saved only when the listener saved that track during the playlist's week — from week_start through the sixth day after it, both included. A track saved earlier was not discovered by the playlist.
For the playlist of the week starting 2024-09-02, return one row per listener who received it, with the columns listener_id, recommended (tracks in their playlist), saved and save_rate_pct — saved as a percentage of recommended, rounded to 2 decimal places. Order by save_rate_pct from highest to lowest, then by listener_id.
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: DiscoverWeekly
| Column | Type |
|---|---|
| week_start | date |
| listener_id | int |
| track_id | int |
Primary key: week_start, listener_id, track_id.
One row per track recommended to a listener in a week's Discover Weekly; weeks start on Mondays.
Table: SavedTrack
| Column | Type |
|---|---|
| listener_id | int |
| track_id | int |
| saved_on | date |
Primary key: listener_id, track_id.
A track in a listener's Liked Songs and the day it was saved; a track is saved at most once.
Examples
Example 1
DiscoverWeekly
| week_start | listener_id | track_id |
|---|---|---|
| 2024-09-02 | 1 | 11 |
| 2024-09-02 | 1 | 12 |
| 2024-09-02 | 1 | 13 |
| 2024-09-02 | 1 | 14 |
| 2024-09-02 | 2 | 11 |
| 2024-09-02 | 2 | 15 |
| 2024-09-02 | 3 | 16 |
| 2024-09-02 | 3 | 17 |
| 2024-09-02 | 3 | 18 |
| 2024-08-26 | 4 | 12 |
| 2024-08-26 | 4 | 19 |
SavedTrack
| listener_id | track_id | saved_on |
|---|---|---|
| 1 | 11 | 2024-09-02 |
| 1 | 13 | 2024-09-08 |
| 1 | 14 | 2024-09-09 |
| 2 | 11 | 2024-08-20 |
| 2 | 15 | 2024-09-05 |
| 4 | 12 | 2024-08-27 |
Output
| listener_id | recommended | saved | save_rate_pct |
|---|---|---|---|
| 1 | 4 | 2 | 50 |
| 2 | 2 | 1 | 50 |
| 3 | 3 | 0 | 0 |
How to solve Spotify: Discover Weekly Save Rate per Listener
Every recommendation of the week is one row of DiscoverWeekly; the question for each is whether a matching save exists inside the week. A LEFT JOIN to SavedTrack on listener, track and the date window answers it row by row: matched rows carry a save, unmatched ones carry NULLs. Grouping by listener, COUNT(*) is the playlist size and COUNT(s.track_id) the number saved.
The window belongs in ON. In WHERE, s.saved_on BETWEEN … would be NULL for unmatched rows and drop them, turning the LEFT JOIN into an inner one and losing every listener who saved nothing — and their recommendations from the denominator. Saves before the week (listener 2's track 11 was liked in August) are not discoveries; a save on the seventh day after week_start is the next week's. Both window ends are inclusive: 2 September and 8 September count.
The rate is 100 * saved / recommended, rounded to two places. The alternative tests each recommendation with a correlated EXISTS and averages the flags, which is the same plan written out. Both are one pass over the week's recommendations with a primary-key probe into the saves.
Reference solution (MySQL)
SELECT d.listener_id,
COUNT(*) AS recommended,
COUNT(s.track_id) AS saved,
ROUND(100 * COUNT(s.track_id) / COUNT(*), 2) AS save_rate_pct
FROM DiscoverWeekly d
LEFT JOIN SavedTrack s
ON s.listener_id = d.listener_id
AND s.track_id = d.track_id
AND s.saved_on BETWEEN d.week_start AND DATE_ADD(d.week_start, INTERVAL 6 DAY)
WHERE d.week_start = '2024-09-02'
GROUP BY d.listener_id
ORDER BY save_rate_pct DESC, d.listener_idAnother way
SELECT listener_id, COUNT(*) AS recommended, SUM(was_saved) AS saved, ROUND(100 * SUM(was_saved) / COUNT(*), 2) AS save_rate_pct
FROM (SELECT d.listener_id, CASE WHEN EXISTS (SELECT 1 FROM SavedTrack s WHERE s.listener_id = d.listener_id AND s.track_id = d.track_id
AND s.saved_on >= '2024-09-02' AND s.saved_on <= '2024-09-08') THEN 1 ELSE 0 END AS was_saved
FROM DiscoverWeekly d WHERE d.week_start = '2024-09-02') x
GROUP BY listener_id
ORDER BY save_rate_pct DESC, listener_id← Spotify: URL Slugs for Podcast Show Names · Spotify: Free Listeners Who Hit the Hourly Skip Cap →