Latest Order Per Customer

SQL coding challenge · Difficulty: easy · +50 XP

Table: orders

+-------------+---------+

| Column      | Type    |

+-------------+---------+

| order_id    | INT     |
| customer_id | INT     |
| order_date  | DATE    |
| amount      | DECIMAL |

+-------------+---------+

Problem

-------

For each customer, retrieve the single

most recent order (by order_date).

If two orders share the same latest date,

pick the one with the higher order_id.

Return: customer_id, order_id, order_date,

amount

Order: customer_id ASC

Example Input

-------------

+----------+-------------+------------+--------+

| order_id | customer_id | date       | amount |

+----------+-------------+------------+--------+

|    1     |     101     | 2024-01-10 | 250.00 |
|    2     |     101     | 2024-03-15 | 480.00 |
|    3     |     101     | 2024-03-15 | 120.00 |
|    4     |     102     | 2024-02-20 | 310.00 |
|    5     |     103     | 2024-01-05 | 190.00 |

+----------+-------------+------------+--------+

Expected Output

+-------------+----------+------------+--------+

| customer_id | order_id | date       | amount |

+-------------+----------+------------+--------+

|     101     |    3     | 2024-03-15 | 120.00 |
|     102     |    4     | 2024-02-20 | 310.00 |
|     103     |    5     | 2024-01-05 | 190.00 |

+-------------+----------+------------+--------+

Explanation

-----------

Customer 101: two orders on 2024-03-15

→ pick order_id=3 (higher id)

Customer 102: only one order → order_id=4

Customer 103: only one order → order_id=5

Hint

----

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date DESC, order_id DESC

)