Spotify: Podcast Episode-to-Episode Listener Retention

Hard
942.

Spotify: Podcast Episode-to-Episode Listener Retention

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

ColumnType
show_idint
show_namevarchar

Primary key: show_id.

One row per show; names are unique.

Table: Episode

ColumnType
episode_idint
show_idint
episode_noint

Primary key: episode_id.

A show's episodes are numbered 1, 2, 3, … with no gaps.

Table: EpisodePlay

ColumnType
play_idint
listener_idint
episode_idint
played_ondate

Primary key: play_id.

One row per play; a listener may play an episode several times.

Example

The rows must come back in the order shown.

PodcastShow

show_idshow_name
1Founders Unfiltered
2Cricket After Hours

Episode

episode_idshow_idepisode_no
1111
1212
1313
2121
2222

EpisodePlay

play_idlistener_idepisode_idplayed_on
1100112024-04-01
2101112024-04-02
3102112024-04-02
4100112024-04-09
5100122024-04-10
6102122024-04-01
7103122024-04-05
8103132024-04-20
9101132024-04-21
10100222024-05-01
1 more row

Output

show_nameepisode_nolistenersretained_pct
Cricket After Hours110
Founders Unfiltered1366.67
Founders Unfiltered2333.33
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: 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

ColumnType
show_idint
show_namevarchar

Primary key: show_id.

One row per show; names are unique.

Table: Episode

ColumnType
episode_idint
show_idint
episode_noint

Primary key: episode_id.

A show's episodes are numbered 1, 2, 3, … with no gaps.

Table: EpisodePlay

ColumnType
play_idint
listener_idint
episode_idint
played_ondate

Primary key: play_id.

One row per play; a listener may play an episode several times.

Examples

Example 1

PodcastShow

show_idshow_name
1Founders Unfiltered
2Cricket After Hours

Episode

episode_idshow_idepisode_no
1111
1212
1313
2121
2222

EpisodePlay

play_idlistener_idepisode_idplayed_on
1100112024-04-01
2101112024-04-02
3102112024-04-02
4100112024-04-09
5100122024-04-10
6102122024-04-01
7103122024-04-05
8103132024-04-20
9101132024-04-21
10100222024-05-01
11104212024-05-02

Output

show_nameepisode_nolistenersretained_pct
Cricket After Hours110
Founders Unfiltered1366.67
Founders Unfiltered2333.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_no

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