Intermediate
Case 22 of 50
Case 022: The Latest Order
orders
A customer calls in asking about "the order I just placed" — support needs to pull it up fast, without scrolling through that customer's entire order history to find it. The manager passes the request to Alex.
"Could you just pull each customer's single most recent order — nothing older?"
The data Alex is looking at
orders
| id | customer_id | order_date |
|---|---|---|
| 101 | 1 | 2026-01-05 |
| 102 | 2 | 2026-01-08 |
| 103 | 3 | 2026-01-12 |
| 104 | 1 | 2026-01-20 |
| 105 | 4 | 2026-02-01 |
| … 23 more rows | ||
TASK
Help Alex answer the manager:
“Find each customer's most recent order only (one row per customer).”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
SELECT id, customer_id, order_date
FROM (
SELECT
orders.*,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) AS ranked
WHERE rn = 1;
What that query returns
Case Debrief
ROW_NUMBER, PARTITION BY, filtering a window result
- PARTITION BY restarts the numbering for each group — here, each customer.
- You can't filter on rn in the same SELECT that creates it — WHERE runs before window functions are calculated, so it can't see rn yet.
- Wrapping the windowed query in a subquery fixes that: the inner query computes rn first, then the outer query's WHERE rn = 1 filters on a column that already exists by that point. This subquery-then-filter shape is one of the most common patterns you'll use with window functions.
- Quick tip: rn here is just an alias — a name you picked with AS, not a special SQL keyword. You can alias any computed column this way to keep the rest of the query short and readable, exactly like total_spent or spending_rank in other cases.