We use analytics and advertising cookies to understand how the site is used and whether our ads on Facebook and Instagram work. They are set only if you accept. See our Privacy Policy for details.
This problem is paper-only - see explanation for the canonical SQL solution.
Given Orders(id, customer_id, order_date, total), return the latest order per customer. If a customer has multiple orders on the same most-recent date, pick the one with the largest id as tiebreaker. Sort the result by customer_id ascending.
For the auto-grader: return [{ id, customer_id, order_date, total }].
A medium sql problem, graded against 6 test cases (4 of them hidden).
Query-writing problems - joins, aggregation, window functions, and query tuning.
Reach for it when you see: A schema and a question about the data rather than a function signature.
More SQL problems →```sql
SELECT id, customer_id, order_date, total
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, id DESC
) AS rn
FROM Orders
) t
WHERE rn = 1
ORDER BY customer_id;
```
ROW_NUMBER vs RANK vs DENSE_RANK:
- `ROW_NUMBER`: 1, 2, 3, 4 - always unique within partition.
- `RANK`: 1, 1, 3, 4 - ties share rank, gaps after.
- `DENSE_RANK`: 1, 1, 2, 3 - ties share rank, no gaps.
For "exactly one row per group" use ROW_NUMBER with deterministic tiebreakers.
The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.
Read off this problem's own test suite, so these are the cases a submission actually has to survive.
These apply to the pattern as a whole, not just this problem.