Completion Rate of Every Series

Medium
482.

Completion Rate of Every Series

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

ColumnType
episode_idint
series_namevarchar
runtime_minutesint

Primary key: episode_id.

One row per episode; several episodes share a series_name.

Table: WatchSession

ColumnType
session_idint
profile_idint
episode_idint
watched_minutesint

Primary key: session_id.

One row per playback session of an episode; episode_id always names a row of Episode.

Example

The rows must come back in the order shown.

Episode

episode_idseries_nameruntime_minutes
1Coastal Cops50
2Coastal Cops40
3Backbenchers20
4Hill Station30

WatchSession

session_idprofile_idepisode_idwatched_minutes
11145
22144
31240
43318
5235
63212

Output

series_namesessionscompletion_pct
Backbenchers250
Coastal Cops450
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.

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

ColumnType
episode_idint
series_namevarchar
runtime_minutesint

Primary key: episode_id.

One row per episode; several episodes share a series_name.

Table: WatchSession

ColumnType
session_idint
profile_idint
episode_idint
watched_minutesint

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_idseries_nameruntime_minutes
1Coastal Cops50
2Coastal Cops40
3Backbenchers20
4Hill Station30

WatchSession

session_idprofile_idepisode_idwatched_minutes
11145
22144
31240
43318
5235
63212

Output

series_namesessionscompletion_pct
Backbenchers250
Coastal Cops450

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_name

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