Spotify: Discover Weekly Save Rate per Listener

Medium
931.

Spotify: Discover Weekly Save Rate per Listener

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

ColumnType
week_startdate
listener_idint
track_idint

Primary key (together): 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

ColumnType
listener_idint
track_idint
saved_ondate

Primary key (together): 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.

Example

The rows must come back in the order shown.

DiscoverWeekly

week_startlistener_idtrack_id
2024-09-02111
2024-09-02112
2024-09-02113
2024-09-02114
2024-09-02211
2024-09-02215
2024-09-02316
2024-09-02317
2024-09-02318
2024-08-26412
1 more row

SavedTrack

listener_idtrack_idsaved_on
1112024-09-02
1132024-09-08
1142024-09-09
2112024-08-20
2152024-09-05
4122024-08-27

Output

listener_idrecommendedsavedsave_rate_pct
14250
22150
3300
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: 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

ColumnType
week_startdate
listener_idint
track_idint

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

ColumnType
listener_idint
track_idint
saved_ondate

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_startlistener_idtrack_id
2024-09-02111
2024-09-02112
2024-09-02113
2024-09-02114
2024-09-02211
2024-09-02215
2024-09-02316
2024-09-02317
2024-09-02318
2024-08-26412
2024-08-26419

SavedTrack

listener_idtrack_idsaved_on
1112024-09-02
1132024-09-08
1142024-09-09
2112024-08-20
2152024-09-05
4122024-08-27

Output

listener_idrecommendedsavedsave_rate_pct
14250
22150
3300

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_id

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