Write a query to find the three most recent orders for each customer.

Answer

Rank each customer's orders by date (most recent first) with ROW_NUMBER(), then keep only the top 3 per customer.

Solution

SELECT customer_id, order_id, order_date
FROM (
  SELECT customer_id, order_id, order_date,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM Orders
) ranked
WHERE rn <= 3;

Step by Step

1. PARTITION BY customer_id restarts the row numbering separately for each customer.
2. ORDER BY order_date DESC within each partition means the most recent order gets rn = 1, the next most recent rn = 2, and so on.
3. The outer query keeps only rows where rn <= 3 - each customer's three most recent orders.

Why RANK() or DENSE_RANK() Can Give a Different, Wrong Answer Here

-- If two orders share the exact same order_date:
-- ROW_NUMBER() forces them to 1 and 2 (arbitrary but distinct)
-- RANK() would give both a rank of 1, then skip to 3 for the next row
-- DENSE_RANK() would give both a rank of 1, then 2 for the next row

For a "top 3 rows" requirement, ROW_NUMBER() is almost always correct, since it guarantees exactly 3 rows come out per customer regardless of ties. RANK()/DENSE_RANK() could return more or fewer than 3 rows for a customer if tied dates exist.

Key Exam Traps

• Choosing the right ranking function is the core of this question - interviewers frequently ask "what if ROW_NUMBER() were replaced with RANK() here?" specifically to test this distinction.
• Tiebreaker for identical timestamps: If two orders from the same customer share an identical order_date, ROW_NUMBER() picks between them arbitrarily unless a secondary ORDER BY column (like order_id) is added - worth adding one for deterministic results.
• Customers with fewer than 3 orders simply return all of their orders - the query never errors or pads missing rows.