Latest Order per Customer

Hook: Interviewers love this because it checks whether you can turn the word latest into a precise SQL rule instead of guessing with MAX().

Question: How do you get the latest order for each customer?

Answer: You want one row per customer: the order with the newest date, and if two orders share the same date, you need a tie-breaker so the result is stable. The most common safe solution is to rank each customer’s orders with ROW_NUMBER(), sort newest first, then keep only row 1.

Interview-Ready Answer: I would partition the orders by customer_id, order each customer’s rows by order_date DESC and a second key like order_id DESC, then filter to ROW_NUMBER() = 1. That gives exactly one latest order per customer and avoids the classic bug where a simple MAX(order_date) join returns duplicate rows when two orders have the same timestamp.

What the problem really means

“Latest” means the most recent order in time for each customer. In SQL, that sounds simple, but the tricky part is returning the whole row, not just the date. A date by itself tells you when the latest order happened; it does not tell you which order ID, amount, or status belongs to that date.

How the window-function solution works

1. Split the rows into groups using PARTITION BY customer_id. A partition is just a logical mini-table for each customer.
2. Sort each group with ORDER BY order_date DESC, order_id DESC. Newest dates come first, and the extra sort key breaks ties in a deterministic way.
3. Assign a rank number with ROW_NUMBER(). The first row in each customer group gets 1, the next gets 2, and so on.
4. Keep only the first row by filtering WHERE rn = 1. That leaves one latest order per customer.

Why ROW_NUMBER() is usually the best answer

ROW_NUMBER() is a window function, which means it computes a value across related rows without collapsing them into one row the way GROUP BY does. That is exactly what we need here: preserve the full row, but still know which row is the newest.

Performance and complexity

Most databases will scan the relevant rows once and sort within each customer partition. In practice, the cost is often close to O(n log n) because of sorting, though a helpful index can reduce the work a lot. A composite index like (customer_id, order_date DESC, order_id DESC) is the usual performance booster because it matches the partition and sort order.

If you only have a few thousand rows, any correct method is fine. On millions of rows, the window-function approach is still preferred because it is predictable and usually easier for the optimizer to reason about than a self-join on aggregates.

Important edge cases

• Ties on the same timestamp: without a second sort key, the database may return either row.
• NULL dates: decide whether they should be ignored; usually they should not count as “latest”.
• Cancelled orders: if business rules say “latest completed order,” filter them out before ranking.
• Customers with no orders: this query returns only customers that actually have at least one order.
• Time zones: if orders come from multiple regions, store and compare times consistently, usually in UTC.

When to use alternatives

Use a MAX() join only when you know the latest timestamp is unique per customer, or when the table is tiny and the rule is safe. Use a correlated subquery if your team finds it easier to read, but be careful: some engines will execute it row by row unless the optimizer rewrites it well.

Memory trick: think of each customer as a stack of order cards. Sort each stack newest-to-oldest, then take the top card. That is exactly what ROW_NUMBER() = 1 does.

Real-World Story: Imagine a checkout service for an e-commerce app. The support team wants a dashboard that shows the latest order per customer so they can answer, “What did this user buy most recently?” The backend uses this query to prefill customer service screens and shipping lookups.

Now imagine a developer writes the classic MAX(order_date) plus join-back query, but two orders share the exact same timestamp because the system batches writes. The dashboard suddenly shows duplicate latest orders for some customers, support agents click the wrong row, and a shipment note is attached to the wrong order. In logs, you might see repeated customer IDs for the same “latest” result, and in the UI the customer card could flicker between two orders depending on load order.

The fix is not just “use a different query”; it is to make the business rule explicit. If two orders tie, decide whether the higher order_id, newer created_at, or some other column wins. Once that rule is written into the ORDER BY, the result becomes stable and safe for production.

Follow-up & Tricky Questions:

• How would you do this without window functions? Use MAX(order_date) per customer in a CTE, then join back to the orders table. It works, but you must add a tie-breaker or you may get duplicate rows when more than one order shares the same latest date.
• How do you return the latest 3 orders per customer? Keep the same ROW_NUMBER() logic, but filter with rn <= 3 instead of rn = 1. That is a very common extension of this pattern.
• What index would you add? A composite index on (customer_id, order_date DESC, order_id DESC) helps the database find and sort each customer’s newest rows faster.
• How do you exclude cancelled orders? Add WHERE status <> 'cancelled' inside the ranked CTE so the ranking only considers eligible orders.
• What if two orders have the same date? Add a second sort key such as order_id DESC or created_at DESC so the result is deterministic.
• Does MAX(order_date) alone solve the problem? No. It gives only the latest timestamp, not the full order row. You still need a second step to pick the matching record.
• What if a customer has no orders? This query will not return that customer at all. If the business wants every customer, start from the customers table and left join the ranked orders.
• Should I use RANK() instead of ROW_NUMBER()? Usually no for this question. RANK() gives the same rank to ties, so you can get multiple rows as “#1”; ROW_NUMBER() gives exactly one row per customer.

Common Mistakes:

• Using MAX(order_date) alone — correction: that only returns a date, not the order row.
• Forgetting a tie-breaker — correction: add a second sort column like order_id DESC so the result is stable.
• Filtering too late — correction: exclude cancelled or invalid rows before ranking if the business rule says they should not count.
• Assuming one latest row is unique by date — correction: real systems often have equal timestamps because of batching or low timestamp precision.

Memory Hook: Picture each customer’s orders as a stack of cards. Sort the stack newest to oldest, then take the top card — that is ROW_NUMBER() = 1.

Cheat Sheet:

• Use ROW_NUMBER() with PARTITION BY customer_id.
• Sort by order_date DESC, then add a tie-breaker.
• Filter to rn = 1 for one row per customer.
• MAX() alone does not return the full row.
• Composite indexes can help a lot on large tables.

Practice Tasks:

• Write the query to get the latest order for each customer using ROW_NUMBER().
• Modify it to return the latest 3 orders per customer.
• Change the rule so cancelled orders are ignored before ranking.