Finding the newest timestamp is easy. Keeping the right row, handling ties, and putting the filter in the right place takes a little more care.
Someone asks for the latest order for each customer, and your brain happily reaches for GROUP BY and MAX.
Honestly, fair enough. The question practically waves a little flag at an aggregate function.
Then the interviewer asks for the order ID, its status, and the amount. Two orders have the same timestamp, and one customer’s newest order is cancelled. Suddenly, finding the latest time is only a small part of the job.
I prefer treating this as a row-selection problem before treating it as a SQL problem. We need to decide which complete record wins, not collect impressive-looking values from different records.
Let’s work through a small original example, then change one sentence in the requirement and watch the answer change with it. There is no giant dataset to hide behind here, which is rather convenient for catching our own assumptions.
Give yourself five rows that refuse to cooperate
Our orders table contains customer_id, id, created_at, status, and total_cents. Each order has a unique ID. Customer IDs, timestamps, and statuses are non-null, and all times shown below are on the same date.
Customer A has three orders. Order 11 was paid at 09:00 for 9,000 cents. Orders 12 and 13 both arrived at 10:00; order 12 is paid for 2,000 cents, while order 13 is cancelled for 3,000 cents.
Customer B has one paid order, ID 21. Customer C has one cancelled order, ID 31.
For the first requirement, return exactly one latest order for every customer represented in this table, regardless of status. If timestamps tie, choose the larger order ID. That tie-breaker is an agreed rule for this exercise, not a claim that a larger ID always represents a later real-world event.
Before writing anything, the expected winning IDs are therefore 13, 21, and 31.
Having that answer on paper makes the query much easier to judge. A plausible result is not enough; we already know which rows the contract requires.
MAX gives you a value, not the rest of its row
This query correctly answers which timestamp is greatest for each customer.
SELECT customer_id, MAX(created_at) AS latest_at FROM orders GROUP BY customer_id; It does not answer which complete order to return. For customer A, two rows share that maximum timestamp.
Joining the result back to orders on customer and timestamp can return both. That may be correct if the request is to return every order tied for latest, but it does not satisfy our exactly-one-row rule yet.
Another tempting repair is to add MAX(total_cents) to the grouped query. Please trace that against customer A before trusting it. You get 10:00 and 9,000 cents, even though no order in the input contains that combination.
Each maximum was calculated independently. The result has quietly assembled a record that never existed, which is quite an achievement for such a short query.
Adding ordinary non-grouped columns is not a reliable solution either. PostgreSQL rejects this form when those columns are not functionally determined by the grouped columns. We need an explicit rule for selecting a row.
Rank the complete records, then keep the winner
Use a window function to number each customer’s orders. The first ordering field defines latest; the second resolves a tie.
WITH ranked AS ( SELECT customer_id, id, created_at, status, total_cents, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY created_at DESC, id DESC ) AS rn FROM orders ) SELECT customer_id, id, created_at, status, total_cents FROM ranked WHERE rn = 1 ORDER BY customer_id; For customer A, order 13 receives row number one, order 12 receives two, and order 11 receives three. The other customers each have one row, so their numbering starts at one too.
All the selected fields now come from the same record. We have not asked the database to guess which status belongs beside an aggregate timestamp.
PostgreSQL’s window-function tutorial explains that ROW_NUMBER numbers rows within a partition and that tied rows have an unspecified numbering order. That is why our unique ID belongs in the ordering clause when exactly one deterministic winner is required.
Also notice the final ORDER BY customer_id. Ordering inside the window decides how to assign row numbers; it does not promise the presentation order of the final result.
If the requirement changes to returning every row tied at the latest timestamp, the ranking rule changes too. A rank based on created_at alone can preserve those ties. Adding a unique ID to that ranking would separate the very ties you were asked to retain.
Moving one filter changes the question
Now suppose the request becomes latest paid order per customer.
We should exclude cancelled rows before ranking, so the eligible population contains only paid orders. In the previous query, add WHERE status = 'paid' immediately after FROM orders inside the CTE.
Customer A now returns order 12. Customer B still returns order 21. Customer C has no paid order, so C has no result under this version of the contract.
But here is a different request that sounds dangerously similar. Return each customer’s latest order, only when that latest order is paid.
This time, rank all orders first. Then use WHERE rn = 1 AND status = 'paid' in the outer query. Customer A disappears because A’s winning row is cancelled. Only customer B remains.
Both queries run. Neither needs to throw an error. They answer different questions because one filter determines who may compete, while the other checks the winner after the competition.
That is the sentence I want to be able to explain in an interview. Memorizing the window-function syntax helps, but it cannot decide which population the business meant.
Keep the missing customer when the requirement asks for them
So far, our population comes from orders. A customer with no orders is invisible, and a customer with no eligible paid order disappears when we filter before ranking.
If the output must contain every customer, start from a separate customers table and left join the selected order onto it. Keep rn = 1 in the join condition or in a prefiltered winners relation.
Putting WHERE ranked.rn = 1 after that left join removes the null-extended rows, so customers without a match disappear again. That would undo the inclusion rule you were trying to implement.
Decide how to represent the absence of an order. Null order fields mean no match; a made-up zero amount would blur that distinction with a real zero-value order.
If timestamps can be null in the actual dataset, define their meaning before removing our non-null assumption. Unknown time is not automatically the oldest or newest event, and default null ordering differs across databases.
Make the counterexamples do the reviewing
The quickest useful check is to compare the exact selected IDs and their associated values, not just the number of output rows.
For this dataset, the three versions should produce these results.
- Latest order regardless of status returns A → 13, B → 21, C → 31.
- Latest paid order returns A → 12 and B → 21.
- Latest order, only if paid, returns B → 21.
Then add a customer with no orders, create another timestamp tie, and change which tied row is paid. If your query still matches the written rule, you have better evidence than a clean run on a perfectly polite dataset.
For production data, inspect the execution plan rather than promising that a window function is automatically cheap. An index beginning with (customer_id, created_at DESC, id DESC) may help the ordering access path, but filters, row counts, selected columns, and the database planner still matter. This all-customer query may process many rows even when it returns few.
For a useful next variation, try this salary-history and top-earner exercise. It asks you to identify the latest employee-department record before choosing the highest-paid employee in each department. That adds a second selection stage, so getting the first population right becomes even more important.
I like leaving this kind of practice with a small question I can reuse. Am I calculating a property of a group, or selecting a particular member of that group?
Once that distinction is clear, the query becomes much easier to explain. More importantly, every timestamp, status, and amount in the answer can point back to the row that actually earned its place.
~ Emily
