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.
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 | 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.
Problem Overview: The task asks you to return the most recent order for each product from an orders table. If a product has multiple orders, you only keep the row with the latest order_date. The result must include the product and the order details associated with that most recent purchase.
Approach 1: Correlated Subquery with MAX(Date) (O(n^2) time, O(1) space)
A direct approach is to compare each row with the maximum order date for its product. For every order row, run a correlated subquery that computes MAX(order_date) for the same product_id. If the current row's date matches that maximum, it belongs in the result. This works because the subquery isolates the most recent timestamp per product. However, the database may evaluate the subquery repeatedly for many rows, which leads to O(n^2) behavior in the worst case. This approach is easy to understand but less efficient on large datasets.
Approach 2: Equi-Join + Window Function (O(n log n) time, O(n) space)
A more scalable solution uses a window function to rank orders within each product group. Compute ROW_NUMBER() over a partition of product_id, ordered by order_date DESC. The window function assigns rank 1 to the newest order for each product. After ranking, filter the result to keep only rows where the rank equals 1. This avoids repeated subqueries and lets the database engine process partitions efficiently using sorting. Many SQL engines optimize this pattern well, making it the standard solution for "latest record per group" problems.
The technique relies on window functions to perform ranking and often pairs with SQL joins or derived tables to structure the final result. You partition rows by product, order them by recency, and extract the top record from each partition.
Recommended for interviews: The window function solution is what most interviewers expect for modern SQL. The correlated subquery shows that you understand grouping and aggregation, but the ROW_NUMBER() pattern demonstrates stronger SQL fluency and scales better. Interviewers frequently test "latest record per group" patterns, and using window functions is the cleanest implementation.
We can use an equi-join to join the Orders table and the Products table based on product_id, and then use the window function rank(), which assigns a rank to each product_id in the Orders table based on its order_date in descending order. Finally, we can select the rows with a rank of 1 for each product_id.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery with MAX(order_date) | O(n^2) | O(1) | Small datasets or when window functions are unavailable |
| Equi-Join + Window Function (ROW_NUMBER) | O(n log n) | O(n) | Best general solution for modern SQL engines and interview settings |
LeetCode Medium 1549 "Most Recent Orders for Each Product" Interview SQL Question with Explanation • Everyday Data Science • 2,140 views views
Watch 2 more video solutions →Practice The Most Recent Orders for Each Product with our built-in code editor and test cases.
Practice on FleetCode