Watch 4 video solutions for The Most Recent Three Orders, a medium level problem involving Database. This walkthrough by Everyday Data Science has 720 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
Example 1:
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?
Problem Overview: You have two tables: Customers and Orders. For each customer, return their three most recent orders. The result should include customer details and order information, sorted by the most recent orders per customer.
Approach 1: Correlated Subquery (O(n^2) time, O(1) space)
A direct way is counting how many orders are more recent than the current order for the same customer. For each row in Orders, run a correlated subquery that checks how many rows exist with the same customer_id and a later order_date. If fewer than three such rows exist, the order belongs in the result set. After filtering, join with the Customers table to retrieve customer names. This approach works in any SQL dialect but performs poorly on large datasets because the subquery runs for every order.
This method demonstrates the core idea: ranking orders by recency within each customer group. However, repeated scans make it inefficient compared to modern SQL features.
Approach 2: Equi-Join + Window Function (O(n log n) time, O(n) space)
The practical solution uses a window function to rank orders per customer. Join Orders with Customers using an equi-join on customer_id. Then compute ROW_NUMBER() with PARTITION BY customer_id ORDER BY order_date DESC. This assigns rank 1 to the newest order, rank 2 to the second newest, and so on. Filter rows where the rank is ≤ 3 to keep only the most recent three orders per customer.
Window functions eliminate repeated scans by computing rankings in a single pass after sorting. Databases like MySQL 8+, PostgreSQL, and SQL Server optimize this pattern efficiently. The logic stays clear: partition by customer, order by date, and keep the top three rows.
This solution relies on concepts from SQL, database querying, and window functions. Understanding partitioned ranking functions such as ROW_NUMBER(), RANK(), and DENSE_RANK() is critical for many database interview problems.
Recommended for interviews: The window function approach is the expected answer. It shows you understand partitioned ranking and modern SQL analytics features. Mentioning the correlated subquery demonstrates the baseline reasoning, but the ROW_NUMBER() solution proves you can write scalable SQL.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery | O(n^2) | O(1) | When window functions are unavailable or when demonstrating the ranking logic conceptually |
| Equi-Join + Window Function (ROW_NUMBER) | O(n log n) | O(n) | Best general solution in modern SQL databases supporting window functions |
