Problem

Write a SQL query to find the most recent order for each customer in a database.
Example input:
orders table:
+----+------------+-------------+
| id | customerId | orderDate   |
+----+------------+-------------+
| 1  | 1          | 2019-01-01  |
| 2  | 2          | 2019-01-02  |
| 3  | 1          | 2019-02-01  |
| 4  | 2          | 2019-02-02  |
| 5  | 3          | 2019-01-03  |
+----+------------+-------------+
customers table:
+----+----------+
| id | name     |
+----+----------+
| 1  | John     |
| 2  | Jane     |
| 3  | Joe      |
+----+----------+
Example output:
+------------+----------+-------------+
| customerId | name     | orderDate   |
+------------+----------+-------------+
| 1          | John     | 2019-02-01  |
| 2          | Jane     | 2019-02 -02  |
| 3          | Joe      | 2019-01-03  |
+------------+----------+-------------+

Solution

SELECT o.customerId, c.name, o.orderDate
FROM orders o
JOIN customers c ON o.customerId = c.id
WHERE o.orderDate = (SELECT MAX(orderDate) FROM orders WHERE customerId = o.customerId)

/*

The solution above is optimal because it uses a subquery to find the most recent order date for each customer.

*/