Ranking with window functions
RANK() OVER (ORDER BY x DESC) numbers rows by x without collapsing them; ties share a rank.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Leaderboards, percentiles, running totals, 'top 3 per group': window functions are what separate reporting SQL from basic SQL.
How to think about it
Which rows get numbered, in what order, within what group? Write the query that produces the rows first. Then add the window column: function OVER (PARTITION BY group ORDER BY sort).
Worked example
SELECT title, minutes,The plain columns first.
RANK() OVER (ORDER BY minutes DESC) AS rkLongest film is rank 1; no rows lost.
FROM films;No GROUP BY: every row survives.
Your turn
Number rows per rating by year.
ROW_NUMBER() OVER ( BY rating ORDER BY year)
Run a query against real tables
The trap
RANK skips after ties (1, 1, 3); DENSE_RANK does not (1, 1, 2). Pick the one the report means.
Practise transactions on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.