How do I get the top 3 in each group in SQL, ties included?

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? And if two tie for third place, both get mentioned.

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_NUMBER breaks it, RANK shares it and leaves a gap, DENSE_RANK shares it with no gap.
The filter
Where do you cut? Never in WHERE: in QUALIFY, or in the outer WHERE over a WITH. And never with LIMIT, 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. If there's a tie, the tied products share a place and all of them make the list. 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

Your query (SQL)

Every run starts from fresh copies of the course's tables, so try anything. Tab indents; to leave the editor, press Escape, then Tab.

Ready to run your query.

Run it yourself

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 you would have dropped Concha for a reason you never chose. Here DENSE_RANK with <= 3 gives the same result as RANK, because the tie falls right at the cut. But with a tie higher up, "place 3" under DENSE_RANK would cover more products than you asked for, while under RANK a tie for first place swallows second.

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: 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: You calculated lugar with RANK() OVER (PARTITION BY mes ORDER BY unidades DESC) AS lugar. Where can you write the condition lugar <= 3?

Check yourself: Three products sell 120, 120 and 95 units. What place does DENSE_RANK give the one with 95?

Check yourself: 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: 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?

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.

Write your answer

DuckDB: the QUALIFY clause and its window alias

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.

SQL for Data People