Skip to main content
Talentd

The Most Recent Three Orders - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min read
Practice this problem

Problem Statement

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?

Approach Overview

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.

Solution

We can use an equi-join to join the Customers table and the Orders table based on customer_id, and then use the window function row_number() to sort the orders for each customer by order_date in descending order and assign a row number to each order. Finally, we can filter out the orders with a row number less than or equal to 3.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Correlated SubqueryO(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

Video Solution

LeetCode Medium 1532 Interview SQL Question with Detailed Explanation • Everyday Data Science • 2,722 views views

Watch 4 more video solutions →

Frequently Asked Questions

Is The Most Recent Three Orders easy or hard?
The problem is rated Medium because it requires understanding window functions and partitioned ranking in SQL. Once you know how ROW_NUMBER() works with PARTITION BY and ORDER BY, the query becomes straightforward.
The Most Recent Three Orders Python/Java solution
This problem is a SQL database challenge rather than a typical algorithm implemented in Python or Java. The correct solution is written as an SQL query using JOIN and ROW_NUMBER() to rank orders per customer and filter the top three.
How to solve The Most Recent Three Orders in O(n)?
Pure O(n) is generally not achievable because the query requires ordering orders by date within each customer group. The closest practical solution is using ROW_NUMBER() with PARTITION BY customer_id ORDER BY order_date DESC, which runs in roughly O(n log n) due to sorting.
What is the best approach for The Most Recent Three Orders?
The best approach uses a window function with ROW_NUMBER(). Partition the orders by customer_id and sort each partition by order_date in descending order. Assign row numbers and keep rows where the rank is less than or equal to 3. This runs in about O(n log n) time due to sorting and works efficiently in MySQL 8+, PostgreSQL, and SQL Server.
Is The Most Recent Three Orders asked at Google/Amazon/Meta?
Problems involving window functions and ranking queries frequently appear in database interview rounds at companies like Amazon, Google, and Meta. Variations include finding top N records per group, most recent transactions, or highest scores per category.
What data structure is used in The Most Recent Three Orders?
The solution relies on SQL window functions rather than traditional data structures. Internally, the database engine performs grouped sorting and ranking operations using constructs like ROW_NUMBER() with PARTITION BY.
What is the time complexity of The Most Recent Three Orders?
The optimal window function solution runs in O(n log n) time because the database sorts rows by customer and order date before computing ROW_NUMBER(). Space complexity is O(n) for storing intermediate ranking results. A correlated subquery alternative can degrade to O(n^2) time.

Ready to solve this problem?

Practice The Most Recent Three Orders with our built-in code editor and test cases.

Practice on FleetCode
Previous

The Most Recent Orders for Each Product