Problem Statement
How would you return each customer’s latest order total without losing other customers? Compare two approaches.
Explanation
A window function solution ranks orders per customer by date and then filters to rank equals one. This scans once, computes row-numbers in a partition, and is easy to extend for ties or top-k.
A join solution aggregates to the max order date per customer, then joins back to orders to fetch the row. It is portable but can be trickier with ties or if dates are not unique. Window functions are preferred when available for clarity and performance.
Code Solution
SolutionRead Only
SELECT customer_id, total
FROM (
SELECT customer_id, total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders
) t
WHERE rn = 1;Practice Sets
This question appears in the following practice sets:
