RANK, DENSE_RANK and ROW_NUMBER in SQL
Three ways to number rows in order, which differ only on ties:
ROW_NUMBER(): 1, 2, 3, 4. Ties get different numbers (arbitrarily, unless you add a tie-breaker).RANK(): 1, 2, 2, 4. Ties share a rank, then it skips. Like Excel's RANK.EQ.DENSE_RANK(): 1, 2, 2, 3. Ties share, no gaps.
Syntax
RANK() OVER (PARTITION BY group_col ORDER BY score DESC)Try itEdit the query, then Run
| agent | closed | row_number | rank | dense_rank |
|---|---|---|---|---|
| Emma | 113 | 1 | 1 | 1 |
| Chloe | 100 | 2 | 2 | 2 |
| Asha | 99 | 3 | 3 | 3 |
| Bruno | 94 | 4 | 4 | 4 |
| Diego | 83 | 5 | 5 | 5 |
5 rows
Add PARTITION BY to rank within groups (best product per region, top customer per country), then filter on the rank in an outer query. That's the top N per group recipe.
Your turn
Rank customers by number of orders, most first: customer_id, orders and order_rank using DENSE_RANK. Keep only the top 3 ranks.
Count orders per customer in a WITH, add DENSE_RANK() OVER (ORDER BY orders DESC), then keep order_rank <= 3.
This page on one card
