Completion Rate of Every Series — SQL Joins Problem
A watch session completes an episode when the viewer watched at least 90% of its runtime (watched_minutes ≥ 0.9 × runtime_minutes).
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #482
Problem statement
A watch session completes an episode when the viewer watched at least 90% of its runtime (watched_minutes ≥ 0.9 × runtime_minutes). Commissioning uses each series' completion rate to decide renewals.
Return one row per series with at least one session, with the columns series_name, sessions (all its sessions) and completion_pct — completed sessions as a percentage of sessions, rounded to 2 decimal places. Order by completion_pct from highest to lowest, then by series_name.
Tables
Table: Episode
| Column | Type |
|---|---|
| episode_id | int |
| series_name | varchar |
| runtime_minutes | int |
Primary key: episode_id.
One row per episode; several episodes share a series_name.
Table: WatchSession
| Column | Type |
|---|---|
| session_id | int |
| profile_id | int |
| episode_id | int |
| watched_minutes | int |
Primary key: session_id.
One row per playback session of an episode; episode_id always names a row of Episode.
Examples
Example 1
Episode
| episode_id | series_name | runtime_minutes |
|---|---|---|
| 1 | Coastal Cops | 50 |
| 2 | Coastal Cops | 40 |
| 3 | Backbenchers | 20 |
| 4 | Hill Station | 30 |
WatchSession
| session_id | profile_id | episode_id | watched_minutes |
|---|---|---|---|
| 1 | 1 | 1 | 45 |
| 2 | 2 | 1 | 44 |
| 3 | 1 | 2 | 40 |
| 4 | 3 | 3 | 18 |
| 5 | 2 | 3 | 5 |
| 6 | 3 | 2 | 12 |
Output
| series_name | sessions | completion_pct |
|---|---|---|
| Backbenchers | 2 | 50 |
| Coastal Cops | 4 | 50 |
How to solve Completion Rate of Every Series
Each session has to be compared with the runtime of its own episode, so the query first joins WatchSession to Episode, then groups by series_name — several episodes roll up into one series.
The completion rule becomes a flag inside the aggregate: SUM(CASE WHEN watched ≥ 90% of runtime THEN 1 ELSE 0 END) counts completed sessions, COUNT(*) counts all of them, and 100 * completed / sessions is the rate. Writing the test as watched_minutes * 10 >= runtime_minutes * 9 keeps it in whole numbers, so 45 of 50 minutes — exactly 90% — completes, and 44 does not. AVG of a 100/0 flag is the same percentage in one step.
An inner join is right because series without sessions are not wanted; a series with sessions but no completions shows 0. The plan is a scan of the sessions with a key lookup each, then a sort of the few series rows.
Reference solution (MySQL)
SELECT e.series_name, COUNT(*) AS sessions,
ROUND(100 * SUM(CASE WHEN w.watched_minutes * 10 >= e.runtime_minutes * 9 THEN 1 ELSE 0 END) / COUNT(*), 2) AS completion_pct
FROM WatchSession w
JOIN Episode e ON e.episode_id = w.episode_id
GROUP BY e.series_name
ORDER BY completion_pct DESC, e.series_nameAnother way
SELECT e.series_name, COUNT(w.session_id) AS sessions,
ROUND(AVG(IF(w.watched_minutes >= 0.9 * e.runtime_minutes, 100, 0)), 2) AS completion_pct
FROM Episode e JOIN WatchSession w ON w.episode_id = e.episode_id
GROUP BY e.series_name
ORDER BY completion_pct DESC, e.series_name← Monthly Active Viewers and Minutes per Viewer · Most-Streamed Artist in Every Genre →