How do I get the top 3 in each group in SQL, ties included?
The final challenge: pull the 3 best-selling products of each month, ties included, using the course's four decisions, and measure how much your understanding has changed.
Free course. Your Maestro, an AI, .
This is the course's final challenge. The ventas_productos table has the units sold of six products at a small family restaurant, from January through April 2025. The business question: what were the 3 best-selling products each month? We'll use competition ranks: ties share a rank and leave gaps, as in 1, 1, 3. All products ranked up to 3 are included, even if a tie means more than three products make the list.
Every problem in this course came down to the same four decisions, in this order. Make them before you write a single line:
The partition
Within which group is each row compared? That goes in
PARTITION BY. If the question says "for each" or "per," there's your partition.The order
What puts one row before another, and in which direction? That goes in the inner
ORDER BY. If ties are possible, decide whether you need a tiebreaker.The function
What happens to a tie?
ROW_NUMBERbreaks it,RANKshares it and leaves a gap,DENSE_RANKshares it with no gap.The filter
Where do you cut? Never in
WHERE: inQUALIFY, or in the outerWHEREover aWITH. And never withLIMIT, which cuts the whole result.
First, see the four decisions made on data you already know. Open the steps one at a time, and before opening each one, try to say what comes next. If your first idea for the challenge is GROUP BY mes with MAX(unidades), notice what that would give you: a single number per month, with no product name and no second or third place. That's what a window is for: it ranks without merging rows.
Each branch's 2 best months
Worked example · 4 steps
Using ventas_mensuales: for each branch, its 2 months of 2025 with the highest sales, showing mes, ventas and its place.
Work out the next step yourself, then open it to compare.
Try it
Answer the question below and write your query. If you get stuck on one of the four decisions, tell your Maestro which one.
Check yourself: In February, Café de olla and Concha sold 134 units each, tied for third place. If you use ROW_NUMBER() OVER (PARTITION BY mes ORDER BY unidades DESC, producto) and QUALIFY ... <= 3, what happens in February?
Your turn
Return mes, producto, unidades and lugar (the product's place within its month, from most to fewest units) only for the products in places 1 through 3 each month. Use competition ranks: tied products share a place and subsequent places leave gaps. Include all products whose place is 1, 2 or 3. Sort by mes, lugar, producto. You're done when you see 3 products per month, except for one month with a tie.
Tables: ventas_productos (mes, producto, unidades)
ventas_productos · 24 rows
| Column | Type |
|---|---|
| mes Primer día del mes. | DATE |
| producto Nombre del producto. | VARCHAR |
| unidades Unidades vendidas en el mes. | BIGINT |
First rows
| mes | producto | unidades |
|---|---|---|
| 2025-01-01 | Café de olla | 56 |
| 2025-01-01 | Concha | 128 |
| 2025-01-01 | Tamal | 149 |
Tab indents; Esc then Tab leaves
If you chose RANK, February has 4 products: Café de olla and Concha sold 134 units each and share third place. Here's February under all three functions, from a sandbox run (ROW_NUMBER with producto as the tiebreaker):
| producto | unidades | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|---|
| Chilaquiles | 155 | 1 | 1 | 1 |
| Pan de elote | 151 | 2 | 2 | 2 |
| Café de olla | 134 | 3 | 3 | 3 |
| Concha | 134 | 4 | 3 | 3 |
| Atole | 98 | 5 | 5 | 4 |
| Tamal | 46 | 6 | 6 | 5 |
With ROW_NUMBER and producto as the tiebreaker, Concha would be excluded by the alphabetical rule, not because it sold less. Here DENSE_RANK with <= 3 gives the same result as RANK, because the tie falls right at the cut. But they answer different ranking policies. With sales of 200, 200, 150 and 140, RANK assigns 1, 1, 3, 4: the <= 3 filter keeps three products. DENSE_RANK assigns 1, 1, 2, 3 and keeps all four because it selects the three distinct sales levels. The challenge asks for the first policy; if the business wanted three distinct levels, the choice would change.
It's one of the most common interview questions: "the 3 best-selling products in each category," "the 2 highest-paid employees in each department" or "the top 5 songs per country." Before you write anything, ask what happens with a tie: that question, asked out loud, is already half the answer they're looking for. If you want to rehearse it, ask your Maestro for a version with different data.
Your task for this week: find a report that says "top" or "leading" and check how it cuts. If it uses LIMIT on a result with several groups, or ROW_NUMBER without anyone deciding what happens with a tie, you know what to ask.
How much has your understanding changed?
On the first page you answered five questions before you started. Here they are again, in different words and with different data.
Check yourself, 1 of 5: A table has 24 rows covering 4 months. You add AVG(unidades) OVER (PARTITION BY mes) to the SELECT, with no GROUP BY. How many rows come back?
Check yourself, 2 of 5: You calculated lugar with RANK() OVER (PARTITION BY mes ORDER BY unidades DESC) AS lugar. Where can you write the condition lugar <= 3?
Four products sell 200, 200, 150 and 140 units. Write their ranks with RANK and DENSERANK. How many products does lugar <= 3 keep with each function? If you are asked for the three distinct sales levels, which would you choose and why?
Check yourself, 3 of 5: Four products sell 200, 200, 150 and 140 units. Write their ranks with RANK and DENSE_RANK. How many products does lugar <= 3 keep with each function? If you are asked for the three distinct sales levels, which would you choose and why?
Check yourself, 4 of 5: You build a running total with SUM(unidades) OVER (ORDER BY mes), and there are six rows per month. What do the six January rows show?
Check yourself, 5 of 5: A customer bought in January, February and April, but not in March. With LAG(fecha) OVER (ORDER BY fecha), what comes out on the April row?
Think of a query or a report from your job. Where would you use one of these windows first, and which trap from this course (ties, a missing month, the default frame, or the WHERE that runs before the window) would you need to check before trusting the result?
Reflect: Think of a query or a report from your job. Where would you use one of these windows first, and which trap from this course (ties, a missing month, the default frame, or the WHERE that runs before the window) would you need to check before trusting the result?
Only you and your Maestro read this. It's kept on your account and deleted with it.
What comes next
You can now put a group's total next to every row, number rows and keep one per group, compare with the previous row knowing that LAG counts rows and not months, accumulate and smooth by choosing your frame, and say when each piece runs. That covers most of the window-function queries you'll see in reports and in interviews.
The best next step is to take one of these queries to your own data at work, with your Maestro beside you. This course doesn't teach joins or how to create data: if GROUP BY, joins or WITH felt shaky along the way, the course SQL for Data People works through them in depth, starting from what a database is.