Rank each customer's orders without a self-join
For every customer, return their three most recent orders with a per-customer rank of 1, 2, 3.
The obvious solution is a correlated subquery counting later orders. On the real table — eleven million rows — it does not finish. Write the version that does.
Example
- input
orders(id, customer_id, placed_at, total)outputcustomer_id, id, placed_at, total, rnrn restarts at 1 for each customer, ordered by placed_at descending.
Constraints
- One pass over orders. No correlated subquery.
- Ties on placed_at must not produce two rows with rn = 1.
- Break a tie with the higher id first, so the ranking is deterministic. Without a tiebreak the query has more than one correct answer, which is a bug in production and not only in the test.
Hints
Hint 1
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...).
Hint 2
RANK() and ROW_NUMBER() differ exactly on ties — the constraint tells you which one you need.
Hint 3
You cannot filter on a window function in WHERE. It has not been computed yet.
Stuck? The lesson behind this problem: 🐘 Window functions
sql
Tab indents · Escape first to tab out
Test cases
These are the specification. Run tests checks your answer against them.
| Case | Input | Expected |
|---|---|---|
| three per customer | 2 customers × 5 orders | 6 rows, rn ∈ {1,2,3} |
| a customer with one order | 1 order | 1 row, rn = 1 |
| identical timestamps | 2 orders, same placed_at | rn = 1 and 2, never 1 and 1 |