The Most Recent Orders for Each Product - Problem

You are given three tables: Customers, Orders, and Products. The Customers table contains customer information, the Orders table tracks all orders with dates and product details, and the Products table contains product information including names and prices.

Write a SQL query to find the most recent order(s) for each product. If multiple orders for the same product have the same most recent date, include all of them.

Return the result ordered by:

  • product_name in ascending order
  • In case of a tie, by product_id in ascending order
  • If there's still a tie, by order_id in ascending order

Table Schema

Customers
Column Name Type Description
customer_id PK int Unique identifier for each customer
name varchar Customer name
Primary Key: customer_id
Orders
Column Name Type Description
order_id PK int Unique identifier for each order
order_date date Date when the order was placed
customer_id int Foreign key referencing customer
product_id int Foreign key referencing product
Primary Key: order_id
Products
Column Name Type Description
product_id PK int Unique identifier for each product
product_name varchar Name of the product
price int Price of the product
Primary Key: product_id

Input & Output

Example 1 — Multiple Products with Different Recent Dates
Input Tables:
Customers
customer_id name
1 Alice
2 Bob
3 Charlie
Orders
order_id order_date customer_id product_id
1 2022-01-25 1 1
2 2022-01-01 2 2
3 2022-01-25 3 1
4 2022-01-11 2 2
5 2022-01-08 1 3
Products
product_id product_name price
1 Bush's Best Original Baked Beans 2
2 Arrowhead Mills Cereal 3
3 Kraft Real Mayo 5
Output:
order_id order_date customer_id product_id product_name price
4 2022-01-11 2 2 Arrowhead Mills Cereal 3
1 2022-01-25 1 1 Bush's Best Original Baked Beans 2
5 2022-01-08 1 3 Kraft Real Mayo 5
💡 Note:

For product 1 (Bush's Best Original Baked Beans), the most recent order is order_id 1 and 3 both on 2022-01-25, but order_id 1 comes first when ordered by order_id. For product 2 (Arrowhead Mills Cereal), the most recent order is order_id 4 on 2022-01-11. For product 3 (Kraft Real Mayo), there's only one order (order_id 5). Results are ordered by product_name alphabetically.
Example 2 — Tie in Most Recent Date
Input Tables:
Customers
customer_id name
1 Alice
2 Bob
Orders
order_id order_date customer_id product_id
1 2022-01-25 1 1
2 2022-01-25 2 1
3 2022-01-20 1 1
Products
product_id product_name price
1 Laptop 1000
Output:
order_id order_date customer_id product_id product_name price
1 2022-01-25 1 1 Laptop 1000
2 2022-01-25 2 1 Laptop 1000
💡 Note:

Product 1 (Laptop) has two orders on the most recent date (2022-01-25): order_id 1 and 2. Both are included in the result since they share the same most recent date. They are ordered by order_id (1 comes before 2).

Constraints

  • 1 ≤ customer_id, order_id, product_id ≤ 1000
  • There will be no product ordered by the same user more than once in one day
  • order_date is a valid date
  • product_name and customer name consist of English letters and spaces

Visualization

Tap to expand
Most Recent Orders Problem OverviewOrders Tableorder_idproduct_idorder_datern112022-01-251312022-01-251422022-01-111532022-01-081ROW_NUMBER()PARTITION BY product_idResult: Most Recent Ordersorder_idproduct_idorder_dateproduct_nameprice422022-01-11Arrowhead Mills3112022-01-25Bush Best Beans2532022-01-08Kraft Real Mayo5
Understanding the Visualization
1
Partition
Group orders by product_id
2
Rank
ROW_NUMBER by date DESC
3
Filter
Select rank = 1
Key Takeaway
🎯 Key Insight: Use window functions to efficiently find maximum values per group without complex subqueries

Related Problems

Asked in
Amazon 12 Google 8 Microsoft 6
28.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen