Latest Order Per Customer

Hook: This is the SQL version of asking, 'Which package arrived last for each person?' Interviewers love it because it tests grouping, sorting, and whether you can keep the full row, not just the date.

Question: How do I return the latest order for each customer?

Answer: Use a subquery or, more commonly, a CTE with ROW_NUMBER(). Partition the rows by customer, sort each customer’s orders from newest to oldest, then keep only row number 1. That gives you one deterministic latest order per customer, even when you add a tie-breaker.

Interview-Ready Answer: I’d write a CTE that ranks orders per customer with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC), then select the rows where rn = 1. I like this approach because it returns the full latest row, not just the max date, and the extra order_id tie-breaker makes the result deterministic when two orders share the same timestamp.

What it is

Detailed Explanation: A CTE (Common Table Expression) is a named temporary result set used inside one SQL statement. The core idea here is: do not collapse orders too early. If you only ask for MAX(order_date), you get a date, but you lose the rest of the row. Instead, rank all orders inside each customer group and keep the first row.

How it works under the hood

1. Read every row from the orders table.
2. Split the rows into groups by customer_id; this is called partitioning, which means dividing rows into buckets that share the same key.
3. Sort each customer’s bucket by newest first, usually order_date DESC.
4. Add a sequence number with ROW_NUMBER(); the newest row gets 1, the next gets 2, and so on.
5. Filter to rn = 1 to keep only the latest order for each customer.

That mental model is easy to replay in an interview: group, sort, rank, keep the first.

Why not just use MAX(order_date)?

MAX(order_date) gives the latest date, but not the full order row. To get the full row, many people join back to the table, and that is where bugs appear. If two orders share the same max timestamp, the join returns both rows. If a customer only has NULL dates, a normal equality join can return nothing because NULL = NULL is not true in SQL.

When and why to use it

Use this pattern whenever you need the latest row per parent key: latest order, latest login, latest event, latest status change. It is especially useful when the row has more columns than just the timestamp, because the ranking step keeps the whole row intact.

Performance and complexity

Without an index, the database usually has to sort a lot of rows, so the work is roughly O(n log n). With a composite index like (customer_id, order_date DESC, order_id DESC), the engine may do far less work, especially on large tables. On a table with millions of orders, that index can be the difference between a quick index-assisted plan and a big sort that spills to disk if memory is tight. In PostgreSQL, CTE behavior also changed over time: in version 11 and earlier, a CTE was often an optimization fence; from 12 onward, the planner can inline it when safe, which often makes CTEs behave more like readable subqueries.

Important edge cases

• Same timestamp: add a tie-breaker like order_id DESC so the result is deterministic.
• NULL dates: decide whether they should be ignored or treated as oldest; be explicit.
• Time zones: compare timestamps in one consistent timezone, usually UTC.
• Business meaning: sometimes 'latest' means latest paid order, not latest created order, so the filter column matters.

Real-World Example: Imagine a checkout service for an e-commerce site. The customer profile page shows a 'most recent order' card, and the support team uses the same query to see the last purchase before a refund. If the team writes the query as MAX(order_date) JOIN orders, a Black Friday customer with two orders in the same second may see duplicate latest orders, or a customer with one imported legacy order with a null timestamp may disappear from the result entirely.

In production, that bug shows up as confusing UI cards, duplicate shipments in support dashboards, and logs like 'multiple rows returned for customer_id 1842'. Users notice stale or duplicated order summaries, support agents open the wrong order, and refund workflows can attach to the wrong row. The fix is not just 'make it work'; it is to choose a ranking strategy that matches the business rule and breaks ties clearly.

Follow-up & Tricky Questions:

• How would you get the latest 3 orders per customer? Use the same ROW_NUMBER() pattern, but filter rn <= 3 instead of rn = 1. That keeps a small ranked history for each customer.
• What if two orders have the same timestamp? Add a second sort key such as order_id DESC or created_at DESC, order_id DESC. Without a tie-breaker, the database may pick either row, and the result is not stable.
• When would you prefer a correlated subquery? For small tables or simple filters, a correlated subquery can be easy to read. But for 'full row per group' problems, ROW_NUMBER() is usually clearer and more flexible.
• How do indexes help this query? A composite index on (customer_id, order_date DESC, order_id DESC) can reduce sort work and speed up finding the first row in each group. It does not remove the need to inspect the rows, but it can make the plan much cheaper.
• Is a CTE always materialized? No. In modern PostgreSQL, CTEs can be inlined when safe, so they often behave like readable subqueries. Older PostgreSQL versions treated them more like a forced temporary step.
• Why is MAX(order_date) + join a gotcha? Because it can return multiple rows on ties and can miss customers whose only date is NULL. It answers 'what is the date?' but not reliably 'which row is the latest order?'.
• What should happen with NULL order dates? Decide the business rule first. Most teams treat them as oldest or ignore them, then make that explicit in the ORDER BY so the query behaves predictably.
• Does window ranking scan the whole table? Usually yes, because the database must compare rows within each customer partition. On large data sets, the difference comes from good indexing and avoiding unnecessary wide sorts.

Common Mistakes:

• Using MAX(order_date) alone. Correction: that only gives the date, not the full order row.
• Joining on the max date without a tie-breaker. Correction: identical timestamps can produce duplicate 'latest' rows, so add a deterministic secondary sort.
• Forgetting about NULL timestamps. Correction: decide whether nulls should sort first, last, or be filtered out, and write it explicitly.
• Confusing readability with performance. Correction: a CTE is often cleaner to read, but the real performance win usually comes from the right index and a deterministic sort.

Memory Hook: Think of each customer as a line at a coffee shop: sort the line so the newest customer is at the front, then take the first person. That is exactly what ROW_NUMBER() OVER (...) = 1 does.

Cheat Sheet:

• Partition by customer_id.
• Sort newest first with ORDER BY order_date DESC.
• Add a tie-breaker like order_id DESC.
• Keep only ROW_NUMBER() = 1.
• Use a CTE for readability; a subquery works too.
• Index on (customer_id, order_date DESC, order_id DESC) when this query is frequent.

Practice Tasks:

• Write the query for the latest order per customer without using a CTE, only an inline subquery.
• Modify the query to return the latest two orders per customer.
• Add a filter so only paid orders are considered 'latest'.