Find each customer’s latest order
Return the latest order for every non-NULL customer_id represented in orders. Break same-date ties by greatest order ID; a customer table match is not required. Return only the requested columns and follow the specified ordering. This question probes row_number orders each customer history by recency with a deterministic id tiebreaker.
Input
Setup script defining customers(id INT, name VARCHAR(30)); orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)).
Output
customer_id, id, order_date, amount; ascending customer_id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'Ana'),(2,'Ben'),(3,'Cara'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,100,'2024-01-01'),(2,1,200,'2024-02-01'),(3,2,100,'2025-01-01'),(4,NULL,50,'2024-12-31');
Expected result
customer_id | id | order_date | amount 1 | 2 | 2024-02-01 | 200 2 | 3 | 2025-01-01 | 100
Another dataset (1)
CREATE TABLE customers(id INT, name VARCHAR(30)); INSERT INTO customers VALUES (1,'A'); CREATE TABLE orders(id INT, customer_id INT, amount INT, order_date VARCHAR(10)); INSERT INTO orders VALUES (1,1,10,'2024-01-01'),(2,1,20,'2024-01-01'),(3,NULL,5,'2024-02-01');
Expected result
customer_id | id | order_date | amount 1 | 2 | 2024-01-01 | 20
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Partition by customer_id.
- Use descending date and descending ID.
Target complexity: time O(n log n) · space O(n)