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 customers.

Table: Orders

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| order_id      | int     |
| order_date    | date    |
| customer_id   | int     |
| cost          | int     |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information about the orders made by customer_id.
Each customer has **one order per day**.

Write a solution to find the most recent three orders of each user. If a user ordered less than three orders, return all of their orders.

Return the result table ordered by customer_name in ascending order and in case of a tie by the customer_id in ascending order. If there is still a tie, order them by order_date in descending 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

Input: 
Customers table:
+-------------+-----------+
| customer_id | name      |
+-------------+-----------+
| 1           | Winston   |
| 2           | Jonathan  |
| 3           | Annabelle |
| 4           | Marwan    |
| 5           | Khaled    |
+-------------+-----------+
Orders table:
+----------+------------+-------------+------+
| order_id | order_date | customer_id | cost |
+----------+------------+-------------+------+
| 1        | 2020-07-31 | 1           | 30   |
| 2        | 2020-07-30 | 2           | 40   |
| 3        | 2020-07-31 | 3           | 70   |
| 4        | 2020-07-29 | 4           | 100  |
| 5        | 2020-06-10 | 1           | 1010 |
| 6        | 2020-08-01 | 2           | 102  |
| 7        | 2020-08-01 | 3           | 111  |
| 8        | 2020-08-03 | 1           | 99   |
| 9        | 2020-08-07 | 2           | 32   |
| 10       | 2020-07-15 | 1           | 2    |
+----------+------------+-------------+------+
Output: 
+---------------+-------------+----------+------------+
| customer_name | customer_id | order_id | order_date |
+---------------+-------------+----------+------------+
| Annabelle     | 3           | 7        | 2020-08-01 |
| Annabelle     | 3           | 3        | 2020-07-31 |
| Jonathan      | 2           | 9        | 2020-08-07 |
| Jonathan      | 2           | 6        | 2020-08-01 |
| Jonathan      | 2           | 2        | 2020-07-30 |
| Marwan        | 4           | 4        | 2020-07-29 |
| Winston       | 1           | 8        | 2020-08-03 |
| Winston       | 1           | 1        | 2020-07-31 |
| Winston       | 1           | 10       | 2020-07-15 |
+---------------+-------------+----------+------------+
Explanation: 
Winston has 4 orders, we discard the order of "2020-06-10" because it is the oldest order.
Annabelle has only 2 orders, we return them.
Jonathan has exactly 3 orders.
Marwan ordered only one time.
We sort the result table by customer_name in ascending order, by customer_id in ascending order, and by order_date in descending order in case of a tie.
**Follow up:** Could you write a general solution for the most recent `n`
orders?

Solution

Method 1 - Window Function (ROW_NUMBER)

We use a window function to rank each order per customer by order date (descending). We then select only the top 3 for each customer. Finally, we join with the Customers table and order the result as required.

Code

1
2
3
4
5
6
7
8

SELECT c.name AS customer_name, o.customer_id, o.order_id, o.order_date
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM Orders
) o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.rn <= 3
ORDER BY c.name ASC, o.customer_id ASC, o.order_date DESC;
1
2
3
4
5
6
7
8

SELECT c.name AS customer_name, o.customer_id, o.order_id, o.order_date
FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) rn
    FROM Orders o
) o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.rn <= 3
ORDER BY c.name ASC, o.customer_id ASC, o.order_date DESC;
1
2
3
4
5
6
7
8

SELECT c.name AS customer_name, o.customer_id, o.order_id, o.order_date
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM Orders
) o
JOIN Customers c ON o.customer_id = c.customer_id
WHERE o.rn <= 3
ORDER BY c.name ASC, o.customer_id ASC, o.order_date DESC;

Explanation

  • We use ROW_NUMBER() to assign a rank to each order per customer, ordered by most recent order date.
  • We filter to keep only the top 3 orders per customer.
  • We join with the Customers table to get the customer name.
  • The final result is ordered by customer name, customer id, and order date as required.

Complexity

  • ⏰ Time complexity: O(N log N) where N is the number of orders (for sorting and windowing).
  • 🧺 Space complexity: O(N) for the window function and join.