Most Recent Order for Each Customer
Problem statement
You are given two tables. customers has one row per customer. orders has one row per order, with the customer who placed it, the date it was placed, and its amount.
For each customer who has placed at least one order, return their most recent order: the order with the latest order_date. Return customer_id, customer_name, order_id, order_date, and amount, sorted by customer_id from smallest to largest.
- If a customer has several orders on their latest date, return the one with the largest
order_id. - All five values in a row must come from the same order.
- Customers with no orders are left out.
- Two customers can have the same name. They are still different customers.
Table schema
Use the same input data with any supported language. Open the Schema tab in the editor to see the generated SQL setup or Pandas DataFrames.
customers
One row per customer.
| Column | Type | Nullable | Description |
|---|---|---|---|
| customer_idPK | Integer | No | — |
| customer_name | Text | No | — |
orders
One row per order, with the customer who placed it, its date, and its amount.
| Column | Type | Nullable | Description |
|---|---|---|---|
| order_idPK | Integer | No | — |
| customer_id | Integer | No | — |
| order_date | Date | No | — |
| amount | Integer | No | — |
Foreign key: customer_id → customers(customer_id)
Expected result
Your query or function must return these columns.
| Column | Type | Nullable | Description |
|---|---|---|---|
| customer_id | Integer | No | — |
| customer_name | Text | No | — |
| order_id | Integer | No | — |
| order_date | Date | No | — |
| amount | Integer | No | — |
Row order: must match exactly. Numeric tolerance: 0.
Constraints
customershas at most300rows, andordershas at most1000rows.customer_idandorder_idare unique integers between1and10^9.customer_nameis non-empty text of at most50characters.- Every
orders.customer_idmatches a row incustomers. order_dateis a date between2000-01-01and2099-12-31.amountis an integer between1and10^6.