INTERACTIVE LECTURES
Develop and defend an analysis
Guided class activity · apply the ideas from the full SQL lessons.
Open the detailed lessons for this activity
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Understand ranking without collapsing rows
Step 3 of 3 · Database: Pixar & Box Office
READ & UNDERSTAND
GROUP BY collapses rows into group summaries. A window function can attach a group calculation or rank while retaining the individual rows. The choice depends on the desired output grain.
ROW_NUMBER assigns positions within an ordering; RANK gives tied values the same rank and leaves gaps. Choose a tie policy deliberately and add a deterministic tie-breaker when unique positions are required.
The current browser SQLite engine does not support window syntax. The runnable exercise below explores the ordering first. Try SELECT id, title, ROW_NUMBER() OVER (ORDER BY year DESC, id) AS position FROM movies in your assigned modern desktop SQLite environment.
Worked example
SELECT id, title, year FROM movies ORDER BY year DESC, id;
The preview places newer release years first and breaks ties by id. It establishes the ordering a ROW_NUMBER example would use in a modern engine.
LIMIT 5 returns exactly five rows when at least five exist. A rank-based rule may include more rows when several movies tie, depending on the chosen ranking function and filter.
Your question
Return id, title, and year for the five most recent movies, ordered by year descending and id ascending.
Explain your evidence
How would “all movies tied within the top five ranks” differ from exactly five rows?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.