SQL Quest › SQL Interview Questions › Subqueries & CTEs
Movies Rated Above the Average
The programming desk wants a shortlist of everything rated above the average of the whole table — not above 8, not above a number somebody remembered, above whatever the average happens to be.
Show exactly these 3 columns, in this order: title, genre, rating. No aliases, no rounding — rating is already the stored value. Include every movie whose rating is strictly greater than the average rating across all movies. Order by rating descending, then title ascending.
The idea: (SELECT AVG(rating) FROM movies) inside your WHERE runs first, collapses to a single number, and your > compares against that number. You met this shape on salaries; it is the same shape here, on a table you already know.
Why not look the average up once and paste it in? Because the moment one rating changes, your literal is wrong and the query still runs — returning a confidently wrong list.
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: 61 rows — every movie above 7.772, best first
Hint
SELECT, Subquery, WHERE, and open the hint there if you stall.Concepts
SELECT Subquery WHERE
Practise the topic: SQL practice questions · CTE practice
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test