How do I number the rows in each group with ROW_NUMBER?

The fonda also takes advance orders: the pedidos table has 60 of them, from six regular customers, from January to March 2025 (synthetic data). What was each customer's first order? Their third? To answer, you need a number on each row that says "this is order 1, 2, 3... for this customer." That's what ROW_NUMBER() does:

• PARTITION BY cliente: each customer starts counting from 1.
• ORDER BY fecha inside OVER: the order in which their orders get counted.

With SUM in the previous module, order didn't matter: the total is the same no matter how the rows arrive. For numbering, it does. Without ORDER BY inside OVER, the number each row gets isn't well defined.

Why pedido_id on line 3? Because Elena has two orders on February 6, 2025: 1014 and 1015. With ORDER BY fecha alone, nothing in your query says which comes first, so don't count on them coming out the same way every time. Adding pedido_id makes the order unique.

Try it

Number each customer's orders from oldest to newest. Return cliente, pedido_id, fecha, monto and num_pedido, and sort the result by cliente, num_pedido. Before you write it, answer the question below. If the numbers don't restart for each customer, ask your Maestro.

Notice that the query has two ORDER BYs, and they do different jobs:

• The one inside OVER (...) decides the order rows get numbered in.
• The one at the end decides the order you see the rows in.

Sometimes the result comes out sorted even without the final one, but that's luck, not a promise. If the order you see the rows in matters, write it at the end.

Row by row

Here are Elena's first six orders from the solution, in Maestro's sandbox. Elena's partition is her 10 orders; ROW_NUMBER() walks through it in fecha, pedido_id order and gives each row its place:

When the query reaches Fer's first row, the count goes back to 1. That's all PARTITION BY does here: it decides where the count restarts. Without it, the 60 orders would run from 1 to 60 in a single sequence.

Three cases you'll run into

Newest to oldest. ORDER BY fecha DESC, pedido_id DESC puts number 1 on the newest order. That's how you answer "the latest" instead of "the first," and it's the basis for the next page.

A missing date. If a row has fecha as NULL, it still lands somewhere in the count. In DuckDB, NULLs go last in both ascending and descending order: we tried it in the sandbox with a column that had a NULL, and it came out last both ways. PostgreSQL behaves differently: its documentation says that with DESC it puts NULLs first. If you number "newest to oldest" in PostgreSQL and some dates are missing, number 1 can be a row with no date. When it matters, write NULLS LAST and stop relying on the database's default.

A sort key that repeats. If the columns in your ORDER BY can repeat, add one that doesn't, like the ID. Without it, another run or another database can give you a different number, and nothing warns you.

The interview version: "For each customer, what was their second purchase?" or "number each user's transactions in chronological order." What they're checking is that you partition by the right column, that the inner ORDER BY has a direction and a tiebreaker, and that you know you can't keep row 2 with WHERE (that's the next page). If you want to practice explaining it out loud, ask your Maestro to quiz you.

Outside of interviews, the most common use of ROW_NUMBER() is removing duplicates. If a table got loaded twice and the same customer shows up on several rows, number them per customer from the most recent load to the oldest, and keep number 1: