Real-world recipes · Sales and marketing
Top 3 in every group
"Best-selling products in each region" is a classic. ROW_NUMBER() OVER (PARTITION BY region ORDER BY units DESC) numbers rows 1, 2, 3... separately inside each region. Then keep rows numbered 3 or less.
Try itEdit the query, then Run
| region | rank_in_region | product | units |
|---|---|---|---|
| Asia Pacific | 1 | Desk Lamp | 131 |
| Asia Pacific | 2 | Noise-Cancel Headset | 75 |
| Asia Pacific | 3 | Gel Pens (10) | 70 |
| Europe | 1 | Desk Lamp | 129 |
| Europe | 2 | Notebook Pack | 65 |
| Europe | 3 | Gel Pens (10) | 53 |
| Latin America | 1 | Desk Lamp | 118 |
| Latin America | 2 | Notebook Pack | 80 |
| Latin America | 3 | Webcam HD | 61 |
| North America | 1 | Desk Lamp | 129 |
Showing 10 of 12 rows. Press Run to see them all.
PARTITION BY is "restart the numbering for each…". Without it, you'd get one ranking across all regions.
Your turn
For each country, find the customer who signed up first. Show country, name and signup_date.
ROW_NUMBER() OVER (PARTITION BY country ORDER BY signup_date, id), keep rn = 1.
This page on one card
