Movies Longer Than the Average Movie — SQL Problem
Editors want an "epic watch" collection: movies that run longer than the average movie in the catalogue.
- Difficulty: Easy
- Topics: Subqueries, Basics
- Dialect: MySQL
- Problem: #480
Problem statement
Editors want an "epic watch" collection: movies that run longer than the average movie in the catalogue. Only movies count towards the average — series rows are ignored — and a movie with runtime_minutes NULL is ignored both in the average and in the answer.
Return the columns title_name and runtime_minutes, ordered by runtime_minutes from longest to shortest, then by title_name alphabetically.
Tables
Table: Title
| Column | Type |
|---|---|
| title_id | int |
| title_name | varchar |
| kind | enum(movie, series) |
| runtime_minutes | int |
Primary key: title_id.
For a series, runtime_minutes is the length of an average episode. It is NULL when not yet known.
Examples
Example 1
Title
| title_id | title_name | kind | runtime_minutes |
|---|---|---|---|
| 1 | Paper Boats | movie | 142 |
| 2 | Coastal Cops | series | 300 |
| 3 | Desert Rose | movie | 118 |
| 4 | Tiger Trail | movie | NULL |
| 5 | Kites Over Jaipur | movie | 96 |
| 6 | The Spice Route | movie | 142 |
| 7 | Rangoli | movie | 104 |
Output
| title_name | runtime_minutes |
|---|---|
| Paper Boats | 142 |
| The Spice Route | 142 |
How to solve Movies Longer Than the Average Movie
The threshold is a single value — the average movie runtime — so a scalar subquery in WHERE computes it once: (SELECT AVG(runtime_minutes) FROM Title WHERE kind = 'movie'). The outer query keeps movies whose runtime is strictly greater. Both queries need the kind = 'movie' filter; without it in the subquery, a 300-minute series row would inflate the average.
NULLs take care of themselves: AVG ignores NULL values (it divides by the count of non-NULL runtimes), and NULL > x is not true, so a movie with an unknown runtime never appears. Equal-to-average movies are not "longer", hence >.
Alternatives: compare runtime * count > sum against a one-row derived table (no division at all), or attach AVG(…) OVER () to every movie row with a window and filter outside. All read the table twice at most; the order uses the title name to settle equal runtimes.
Reference solution (MySQL)
SELECT title_name, runtime_minutes
FROM Title
WHERE kind = 'movie'
AND runtime_minutes > (SELECT AVG(runtime_minutes) FROM Title WHERE kind = 'movie')
ORDER BY runtime_minutes DESC, title_nameAnother way
SELECT t.title_name, t.runtime_minutes
FROM Title t CROSS JOIN (SELECT SUM(runtime_minutes) AS s, COUNT(runtime_minutes) AS c FROM Title WHERE kind = 'movie') a
WHERE t.kind = 'movie' AND t.runtime_minutes * a.c > a.s
ORDER BY t.runtime_minutes DESC, t.title_nameAnother way
WITH movies AS (SELECT title_name, runtime_minutes, AVG(runtime_minutes) OVER () AS avg_rt FROM Title WHERE kind = 'movie')
SELECT title_name, runtime_minutes FROM movies WHERE runtime_minutes > avg_rt ORDER BY runtime_minutes DESC, title_name← Short, Standard and Long Episodes · Monthly Active Viewers and Minutes per Viewer →