Short, Standard and Long Episodes — SQL Problem
The player's "quick watch" filter needs every episode labelled by length.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #479
Problem statement
The player's "quick watch" filter needs every episode labelled by length. An episode under 25 minutes is short, one from 25 to 50 minutes (both included) is standard, and anything over 50 minutes is long. Episodes whose runtime has not been ingested yet have runtime_minutes NULL and are labelled unknown.
Return the columns episode_id, series_name and length_bucket for every episode, in any order.
Tables
Table: Episode
| Column | Type |
|---|---|
| episode_id | int |
| series_name | varchar |
| season_no | int |
| episode_no | int |
| runtime_minutes | int |
Primary key: episode_id.
One row per episode. runtime_minutes is NULL until the video's metadata is processed.
Examples
Example 1
Episode
| episode_id | series_name | season_no | episode_no | runtime_minutes |
|---|---|---|---|---|
| 1 | Backbenchers | 1 | 1 | 22 |
| 2 | Backbenchers | 1 | 2 | 25 |
| 3 | Coastal Cops | 2 | 1 | 50 |
| 4 | Coastal Cops | 2 | 2 | 51 |
| 5 | Monsoon Diaries | 1 | 1 | NULL |
| 6 | Monsoon Diaries | 1 | 2 | 38 |
Output
| episode_id | series_name | length_bucket |
|---|---|---|
| 1 | Backbenchers | short |
| 2 | Backbenchers | standard |
| 3 | Coastal Cops | standard |
| 4 | Coastal Cops | long |
| 5 | Monsoon Diaries | unknown |
| 6 | Monsoon Diaries | standard |
How to solve Short, Standard and Long Episodes
A searched CASE checks its WHEN branches top to bottom and returns the first one that is true, so overlapping ranges are fine as long as they are ordered: once < 25 has failed, <= 50 only sees runtimes from 25 upward, and ELSE catches everything over 50.
NULL needs its own branch. Every comparison with NULL is unknown, never true, so without the IS NULL test a missing runtime would skip the first three branches and fall into ELSE — labelled long, which is wrong. Testing it first (or wrapping a CASE that has no ELSE in COALESCE(…, 'unknown'), as one alternative does) keeps it out.
The boundaries are the other trap: 25 and 50 are both standard. Nested IFs express the same tree. Each row is labelled independently, so the query is one pass over the table.
Reference solution (MySQL)
SELECT episode_id, series_name,
CASE
WHEN runtime_minutes IS NULL THEN 'unknown'
WHEN runtime_minutes < 25 THEN 'short'
WHEN runtime_minutes <= 50 THEN 'standard'
ELSE 'long'
END AS length_bucket
FROM EpisodeAnother way
SELECT episode_id, series_name, IF(runtime_minutes IS NULL, 'unknown', IF(runtime_minutes < 25, 'short', IF(runtime_minutes > 50, 'long', 'standard'))) AS length_bucket FROM EpisodeAnother way
SELECT episode_id, series_name, COALESCE(CASE WHEN runtime_minutes > 50 THEN 'long' WHEN runtime_minutes BETWEEN 25 AND 50 THEN 'standard' WHEN runtime_minutes < 25 THEN 'short' END, 'unknown') AS length_bucket FROM Episode← Subscriptions Renewing in the Next Seven Days · Movies Longer Than the Average Movie →