Problem

Table: Customers

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| customer_id   | int     |
| name          | varchar |
+---------------+---------+
customer_id is the column with unique values for this table.
This table contains information about the customers.

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| product_id    | int     |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information about the orders made by customer_id.
There will be no product ordered by the same user **more than once** in one day.

Table: Products

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| product_name  | varchar |
| price         | int     |
+---------------+---------+
product_id is the column with unique values for this table.
This table contains information about the Products.

Write a solution to find the most recent order(s) of each product.

Return the result table ordered by product_name in ascending order and in case of a tie by the product_id in ascending order. If there still a tie, order them by order_id in ascending order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

Input: 
Customers table:
+-------------+-----------+
| customer_id | name      |
+-------------+-----------+
| 1           | Winston   |
| 2           | Jonathan  |
| 3           | Annabelle |
| 4           | Marwan    |
| 5           | Khaled    |
+-------------+-----------+
Orders table:
+----------+------------+-------------+------------+
| order_id | order_date | customer_id | product_id |
+----------+------------+-------------+------------+
| 1        | 2020-07-31 | 1           | 1          |
| 2        | 2020-07-30 | 2           | 2          |
| 3        | 2020-08-29 | 3           | 3          |
| 4        | 2020-07-29 | 4           | 1          |
| 5        | 2020-06-10 | 1           | 2          |
| 6        | 2020-08-01 | 2           | 1          |
| 7        | 2020-08-01 | 3           | 1          |
| 8        | 2020-08-03 | 1           | 2          |
| 9        | 2020-08-07 | 2           | 3          |
| 10       | 2020-07-15 | 1           | 2          |
+----------+------------+-------------+------------+
Products table:
+------------+--------------+-------+
| product_id | product_name | price |
+------------+--------------+-------+
| 1          | keyboard     | 120   |
| 2          | mouse        | 80    |
| 3          | screen       | 600   |
| 4          | hard disk    | 450   |
+------------+--------------+-------+
Output: 
+--------------+------------+----------+------------+
| product_name | product_id | order_id | order_date |
+--------------+------------+----------+------------+
| keyboard     | 1          | 6        | 2020-08-01 |
| keyboard     | 1          | 7        | 2020-08-01 |
| mouse        | 2          | 8        | 2020-08-03 |
| screen       | 3          | 3        | 2020-08-29 |
+--------------+------------+----------+------------+
Explanation: 
keyboard's most recent order is in 2020-08-01, it was ordered two times this day.
mouse's most recent order is in 2020-08-03, it was ordered only once this day.
screen's most recent order is in 2020-08-29, it was ordered only once this day.
The hard disk was never ordered and we do not include it in the result table.

Solution

Approach

We need to find the most recent order(s) for each product. For each product, we find the maximum order date, then select all orders for that product on that date. We join with the Products table to get the product name and filter out products with no orders.

Code

MySQL
1
2
3
4
5
6
7
8
9

SELECT p.product_name, o.product_id, o.order_id, o.order_date
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
JOIN (
  SELECT product_id, MAX(order_date) AS max_date
  FROM Orders
  GROUP BY product_id
) t ON o.product_id = t.product_id AND o.order_date = t.max_date
ORDER BY p.product_name ASC, o.product_id ASC, o.order_id ASC;
Oracle
1
2
3
4
5
6
7
8
9

SELECT p.product_name, o.product_id, o.order_id, o.order_date
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
JOIN (
  SELECT product_id, MAX(order_date) AS max_date
  FROM Orders
  GROUP BY product_id
) t ON o.product_id = t.product_id AND o.order_date = t.max_date
ORDER BY p.product_name ASC, o.product_id ASC, o.order_id ASC;
PostgreSQL
1
2
3
4
5
6
7
8
9

SELECT p.product_name, o.product_id, o.order_id, o.order_date
FROM Orders o
JOIN Products p ON o.product_id = p.product_id
JOIN (
  SELECT product_id, MAX(order_date) AS max_date
  FROM Orders
  GROUP BY product_id
) t ON o.product_id = t.product_id AND o.order_date = t.max_date
ORDER BY p.product_name ASC, o.product_id ASC, o.order_id ASC;

Explanation

For each product, we find the latest order date and select all orders for that product on that date. We join with the Products table to get the product name and order as required. Products with no orders are excluded.

Complexity

  • ⏰ Time complexity: O(N + M) where N is the number of orders and M is the number of products.
  • 🧺 Space complexity: O(N) for grouping and joining.