Most Recent Order per Customer - Medium SQL Window Functions Interview Problem

Shopify's fulfillment team flagged a gap in their last support review: agents need the most recent order for each customer to quickly resolve enquiries. Using the orders table, return customer_id, order_id, amount, and created_at for each customer's most recent order, ordered by customer_id.

Schema

orders

column	type
id	INTEGER
customer_id	INTEGER
amount	NUMERIC
created_at	DATE

Sample Input

id	customer_id	amount	created_at
1	1	50.00	2024-01-10
2	1	75.00	2024-03-15
3	2	200.00	2024-02-01
4	2	90.00	2024-04-20
5	3	30.00	2024-01-05

Expected Output

customer_id	order_id	amount	created_at
1	2	75.00	2024-03-15
2	4	90.00	2024-04-20
3	5	30.00	2024-01-05

Hints