Movies Longer Than the Average Movie

Easy
480.

Movies Longer Than the Average Movie

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

ColumnTypeValues
title_idint
title_namevarchar
kindenummovie, series
runtime_minutesint

Primary key: title_id.

For a series, runtime_minutes is the length of an average episode. It is NULL when not yet known.

Example

The rows must come back in the order shown.

Title

title_idtitle_namekindruntime_minutes
1Paper Boatsmovie142
2Coastal Copsseries300
3Desert Rosemovie118
4Tiger TrailmovieNULL
5Kites Over Jaipurmovie96
6The Spice Routemovie142
7Rangolimovie104

Output

title_nameruntime_minutes
Paper Boats142
The Spice Route142
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.

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

ColumnType
title_idint
title_namevarchar
kindenum(movie, series)
runtime_minutesint

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_idtitle_namekindruntime_minutes
1Paper Boatsmovie142
2Coastal Copsseries300
3Desert Rosemovie118
4Tiger TrailmovieNULL
5Kites Over Jaipurmovie96
6The Spice Routemovie142
7Rangolimovie104

Output

title_nameruntime_minutes
Paper Boats142
The Spice Route142

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_name

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

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