The Most Recent Orders for Each Product - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon

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

Approach Overview

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.

Solution

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.

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

LeetCode Medium 1549 "Most Recent Orders for Each Product" Interview SQL Question with Explanation • Everyday Data Science • 2,227 views views

Frequently Asked Questions

Is The Most Recent Orders for Each Product easy or hard?

The problem is rated Medium because it requires understanding SQL grouping patterns and window functions. Developers familiar with ROW_NUMBER() and partitioned ranking can solve it quickly, but those relying only on basic aggregation may struggle with efficiency.

The Most Recent Orders for Each Product Python/Java solution

This problem is categorized as a Database problem on LeetCode, so the expected solution is written in SQL rather than Python or Java. The query typically uses ROW_NUMBER() with PARTITION BY product_id and filters the row with rank 1 to retrieve the most recent order.

How to solve The Most Recent Orders for Each Product in O(n)?

Pure O(n) is uncommon because most SQL engines sort rows when evaluating window functions or grouped queries. The practical optimal approach uses ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY order_date DESC) and then filters for row number 1. With proper indexing on product_id and order_date, the query performs very efficiently in practice.

What is the best approach for The Most Recent Orders for Each Product?

The best approach uses a SQL window function such as ROW_NUMBER() partitioned by product_id and ordered by order_date descending. This ranks orders within each product group so the most recent order receives rank 1. Filtering for rank = 1 returns the latest order per product efficiently with about O(n log n) time due to sorting.

Is The Most Recent Orders for Each Product asked at Google/Amazon/Meta?

Problems involving 'latest record per group' are common in SQL interviews at large tech companies such as Amazon, Google, and Meta. Variants often appear in data engineering or analytics interviews where candidates must retrieve the most recent event, order, or status for each entity.

What data structure is used in The Most Recent Orders for Each Product?

The solution relies on relational database operations rather than traditional data structures. Internally it uses SQL window functions like ROW_NUMBER(), partitioning by product_id and ordering rows by date to rank entries within each group.

What is the time complexity of The Most Recent Orders for Each Product?

The optimal SQL solution using ROW_NUMBER() with PARTITION BY product_id typically runs in O(n log n) time because the database sorts rows within partitions by order_date. Space complexity is O(n) for intermediate ranking results. A correlated subquery approach can degrade to O(n^2) on large datasets.

Ready to solve this problem?

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

Practice on FleetCode

The Most Frequently Ordered Products for Each Customer

The Most Recent Three Orders

Problem Info

DifficultyMedium

Acceptance64.9%

Approaches1

Reading time5 min

Asked at

Amazon

Practice this problem

Open in Editor