SQL Quest › SQL Interview Questions › Window Functions
Earliest Movie per Genre
The archive is compiling a 'first-in-genre' retrospective. For each genre, find the earliest movie (minimum year). If two movies in the same genre tie on year, pick the one whose title comes first alphabetically. Show title, year, genre. Sort by genre ascending. A gentle first step into window functions: PARTITION BY splits the data into per-genre groups so ROW_NUMBER starts fresh for each.
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: Drama's earliest: the 1995 entry
Hint
Concepts
SELECT Window Functions ROW_NUMBER PARTITION BY Subquery
Practise the topic: SQL practice questions · Window function practice · CTE practice · Ranking function practice · Advanced SQL interview questions
In these company practice sets
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
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