What are common mistakes on SQL: Latest Order Per Customer (ROW_NUMBER)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Latest Order Per Customer (ROW_NUMBER)

MediumDatabases

Description

> 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 }]`.

Example 1

Input: orders = [{id:1,customer_id:1,order_date:"2026-01-01",total:10},{id:2,customer_id:1,order_date:"2026-02-01",total:20},{id:3,customer_id:2,order_date:"2026-01-15",total:5}]

Output: [{id:2,customer_id:1,order_date:"2026-02-01",total:20},{id:3,customer_id:2,order_date:"2026-01-15",total:5}]

Constraints

- 0 <= orders.length <= 10^4

SQL: Latest Order Per Customer (ROW_NUMBER): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

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 SQLproblems →

If you are stuck

Show the 2-step hint ladder

ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, id DESC) AS rn.
Wrap in a subquery / CTE and keep only rn = 1.

The optimal approach

Show the approach: Canonical SQL:

```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.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.