SQL Quest › SQL Interview Questions › Aggregation & Grouping

Director Consistency Report

MediumFreeQuerying BasicsAggregation & GroupingSubqueries & CTEs

For directors with 3 or more films, show: director, film_count, avg_rating (2 dec), rating_range (max - min — measures consistency), best_film title (correlated subquery), and worst_film title (correlated subquery). 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: avg_rating: 7.25, rating_range: 1.5, best_film and worst_film titles

Hint

Two correlated subqueries in SELECT: (SELECT title FROM movies m2 WHERE m2.director = m.director ORDER BY rating DESC LIMIT 1) and ORDER BY rating ASC LIMIT 1

Concepts

SELECT GROUP BY HAVING Aggregation Subquery GROUP BY + 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.

Director Consistency Report

Schema

Hint

Concepts

In these company practice sets

Related questions