SQL Quest › SQL Interview Questions › Aggregation & Grouping

Consistent Director Analysis

MediumFreeQuerying BasicsAggregation & GroupingSubqueries & CTEs

Find directors with 3 or more movies. Show exactly these 5 columns, in this order: director, movie_count, avg_rating (2 dec), best_film (title of their highest-rated movie — use a correlated subquery), total_runtime_hours (SUM of runtime / 60.0, 1 dec). Sort by avg_rating descending.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

movies

id	title	year	genre	rating	votes	revenue_millions	runtime	director
1	Guardians of the Galaxy	2014	Action	8.1	757074	333.13	121	James Gunn
2	Prometheus	2012	Adventure	7	485820	126.46	124	Ridley Scott
3	Split	2016	Horror	7.3	157606	138.12	117	M. Night Shyamalan

Expected output: Prolific directors with correlated best-film lookup

Hint

Correlated subquery: (SELECT title FROM movies m2 WHERE m2.director = m.director ORDER BY rating DESC LIMIT 1). Use GROUP BY on outer alias m

Concepts

SELECT GROUP BY HAVING Aggregation Subquery

Practise the topic: SQL practice questions · GROUP BY exercises · CTE practice

In these company practice sets

Google · Snowflake

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Consistent Director Analysis

Schema

Hint

Concepts

In these company practice sets

Related questions