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
💡 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 for Each Product INPUT DATA Orders Table order_id prod_id prod_name order_dt 1 101 Laptop 2024-01-15 2 102 Phone 2024-01-20 3 101 Laptop 2024-01-25 4 102 Phone 2024-01-20 5 103 Tablet 2024-01-18 GOAL: Find most recent order(s) for each product Handle ties (same date) ALGORITHM STEPS 1 Find MAX date per product GROUP BY product_id SELECT product_id, MAX(order_date) as max_dt 2 Create subquery/CTE Store max dates temporarily WITH MaxDates AS ( ...subquery... ) 3 JOIN with original table Match prod + date (handles ties) JOIN ON o.product_id = m.product_id AND o.order_date = m.max_dt 4 ORDER BY results product_name, prod_id, order_id ORDER BY prod_name, prod_id, order_id FINAL RESULT Most Recent Orders prod_name prod_id order_id order_dt Laptop 101 3 2024-01-25 Phone 102 2 2024-01-20 Phone 102 4 2024-01-20 Tablet 103 5 2024-01-18 TIE HANDLED: Phone has 2 orders on same date Both order 2 and 4 included OK - Query Complete 4 rows returned Key Insight: Using a self-join or CTE with MAX() aggregate handles ties naturally. When multiple orders share the same maximum date for a product, the JOIN condition matches ALL of them, not just one. This is more reliable than using ROW_NUMBER() which would arbitrarily pick one in case of ties. Alternative: Use RANK() or DENSE_RANK() window function with partition by product_id TutorialsPoint - The Most Recent Orders for Each Product | Optimal Solution

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