Spotify: Podcast Episode-to-Episode Listener Retention
Podcast creators on Spotify want to know where a series loses its audience.
- Difficulty: Hard
- Topics: Joins, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #942
Problem statement
Podcast creators on Spotify want to know where a series loses its audience. For an episode n of a show, its listeners are the distinct listeners who played it, and its retention is the share of them who also played episode n + 1 of the same show — at any time, before or after.
Return one row for every episode that has at least one listener and a next episode in its show, with the columns show_name, episode_no, listeners and retained_pct — retained listeners as a percentage of listeners, rounded to 2 decimal places. The last episode of a show has no next one and is left out. Order by show_name, then episode_no.
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: PodcastShow
| Column | Type |
|---|---|
| show_id | int |
| show_name | varchar |
Primary key: show_id.
One row per show; names are unique.
Table: Episode
| Column | Type |
|---|---|
| episode_id | int |
| show_id | int |
| episode_no | int |
Primary key: episode_id.
A show's episodes are numbered 1, 2, 3, … with no gaps.
Table: EpisodePlay
| Column | Type |
|---|---|
| play_id | int |
| listener_id | int |
| episode_id | int |
| played_on | date |
Primary key: play_id.
One row per play; a listener may play an episode several times.
Examples
Example 1
PodcastShow
| show_id | show_name |
|---|---|
| 1 | Founders Unfiltered |
| 2 | Cricket After Hours |
Episode
| episode_id | show_id | episode_no |
|---|---|---|
| 11 | 1 | 1 |
| 12 | 1 | 2 |
| 13 | 1 | 3 |
| 21 | 2 | 1 |
| 22 | 2 | 2 |
EpisodePlay
| play_id | listener_id | episode_id | played_on |
|---|---|---|---|
| 1 | 100 | 11 | 2024-04-01 |
| 2 | 101 | 11 | 2024-04-02 |
| 3 | 102 | 11 | 2024-04-02 |
| 4 | 100 | 11 | 2024-04-09 |
| 5 | 100 | 12 | 2024-04-10 |
| 6 | 102 | 12 | 2024-04-01 |
| 7 | 103 | 12 | 2024-04-05 |
| 8 | 103 | 13 | 2024-04-20 |
| 9 | 101 | 13 | 2024-04-21 |
| 10 | 100 | 22 | 2024-05-01 |
| 11 | 104 | 21 | 2024-05-02 |
Output
| show_name | episode_no | listeners | retained_pct |
|---|---|---|---|
| Cricket After Hours | 1 | 1 | 0 |
| Founders Unfiltered | 1 | 3 | 66.67 |
| Founders Unfiltered | 2 | 3 | 33.33 |
How to solve Spotify: Podcast Episode-to-Episode Listener Retention
Start by reducing the plays to the set of (listener, show, episode number) facts with SELECT DISTINCT. Listener 100 played episode 1 twice; counted as plays, that would inflate both the denominator and the numerator.
Retention is then a self join of that set: each fact for episode n looks for the same listener's fact for episode n + 1 of the same show. A LEFT JOIN keeps the facts that find no partner, so per episode COUNT(*) is the number of listeners and COUNT(n.listener_id) the number retained. The order of plays in time is irrelevant — listener 102 played episode 2 before episode 1 and still counts as retained.
Which episodes qualify is decided by the catalogue, not the plays: an episode is left out only when its show has no episode n + 1 (the EXISTS on Episode). If the next episode exists but nobody played it, the episode stays with 0% — the clearest drop-off of all. In the example Founders Unfiltered keeps 2 of 3 listeners from episode 1 to 2 (66.67%) and 1 of 3 from 2 to 3; Cricket After Hours keeps neither of its episode-1 listeners. The alternative joins each episode to its successor up front and tests retention per play with a correlated EXISTS inside a conditional COUNT(DISTINCT …).
Reference solution (MySQL)
WITH heard AS (
SELECT DISTINCT p.listener_id, e.show_id, e.episode_no
FROM EpisodePlay p
JOIN Episode e ON e.episode_id = p.episode_id
)
SELECT s.show_name, h.episode_no, COUNT(*) AS listeners,
ROUND(100 * COUNT(n.listener_id) / COUNT(*), 2) AS retained_pct
FROM heard h
JOIN PodcastShow s ON s.show_id = h.show_id
LEFT JOIN heard n
ON n.listener_id = h.listener_id AND n.show_id = h.show_id AND n.episode_no = h.episode_no + 1
WHERE EXISTS (SELECT 1 FROM Episode x WHERE x.show_id = h.show_id AND x.episode_no = h.episode_no + 1)
GROUP BY s.show_id, s.show_name, h.episode_no
ORDER BY s.show_name, h.episode_noAnother way
SELECT s.show_name, e.episode_no,
COUNT(DISTINCT p.listener_id) AS listeners,
ROUND(100 * COUNT(DISTINCT CASE WHEN EXISTS (SELECT 1 FROM EpisodePlay p2 WHERE p2.listener_id = p.listener_id AND p2.episode_id = nx.episode_id)
THEN p.listener_id END) / COUNT(DISTINCT p.listener_id), 2) AS retained_pct
FROM Episode e
JOIN Episode nx ON nx.show_id = e.show_id AND nx.episode_no = e.episode_no + 1
JOIN EpisodePlay p ON p.episode_id = e.episode_id
JOIN PodcastShow s ON s.show_id = e.show_id
GROUP BY s.show_id, s.show_name, e.episode_id, e.episode_no
ORDER BY s.show_name, e.episode_no← Spotify: Artists Growing Year Over Year in Every Market · Spotify: Family Plans That Ever Had Over Six Members at Once →