Short, Standard and Long Episodes

Easy
479.

Short, Standard and Long Episodes

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

ColumnType
episode_idint
series_namevarchar
season_noint
episode_noint
runtime_minutesint

Primary key: episode_id.

One row per episode. runtime_minutes is NULL until the video's metadata is processed.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Episode

episode_idseries_nameseason_noepisode_noruntime_minutes
1Backbenchers1122
2Backbenchers1225
3Coastal Cops2150
4Coastal Cops2251
5Monsoon Diaries11NULL
6Monsoon Diaries1238

Output

episode_idseries_namelength_bucket
1Backbenchersshort
2Backbenchersstandard
3Coastal Copsstandard
4Coastal Copslong
5Monsoon Diariesunknown
6Monsoon Diariesstandard
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.

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

ColumnType
episode_idint
series_namevarchar
season_noint
episode_noint
runtime_minutesint

Primary key: episode_id.

One row per episode. runtime_minutes is NULL until the video's metadata is processed.

Examples

Example 1

Episode

episode_idseries_nameseason_noepisode_noruntime_minutes
1Backbenchers1122
2Backbenchers1225
3Coastal Cops2150
4Coastal Cops2251
5Monsoon Diaries11NULL
6Monsoon Diaries1238

Output

episode_idseries_namelength_bucket
1Backbenchersshort
2Backbenchersstandard
3Coastal Copsstandard
4Coastal Copslong
5Monsoon Diariesunknown
6Monsoon Diariesstandard

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 Episode

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

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