The Most Recent Orders for Each Product - Practice Coding Problems

The Most Recent Orders for Each Product - Problem

Database Medium

🛒 The Most Recent Orders for Each Product

Imagine you're a data analyst for an e-commerce platform, and your manager asks you to generate a report showing the most recent order for each product in your inventory. This information is crucial for understanding recent customer purchasing patterns and product popularity trends.

You have access to three database tables:

Customers - Contains customer information with unique customer_id
Orders - Contains order details including order_date, customer_id, and product_id
Products - Contains product information with product_name and price

Your task is to write a SQL query that finds the most recent order(s) for each product. If multiple orders for the same product were placed on the same most recent date, include all of them.

The results should be ordered by:

product_name (ascending)
If tied, then by product_id (ascending)
If still tied, then by order_id (ascending)

Input & Output

example_1.sql — Basic Case

$ Input: Customers: [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')] Orders: [(101, '2024-03-10', 1, 1), (102, '2024-03-15', 2, 1), (103, '2024-03-12', 3, 2)] Products: [(1, 'Laptop', 999), (2, 'Mouse', 25)]

› Output: [('Laptop', '2024-03-15', 'Bob'), ('Mouse', '2024-03-12', 'Charlie')]

💡 Note: For Laptop (product_id=1), the most recent order is from 2024-03-15 by Bob. For Mouse (product_id=2), the most recent order is from 2024-03-12 by Charlie.

example_2.sql — Tie Case

$ Input: Customers: [(1, 'Alice'), (2, 'Bob')] Orders: [(101, '2024-03-15', 1, 1), (102, '2024-03-15', 2, 1)] Products: [(1, 'Laptop', 999)]

› Output: [('Laptop', '2024-03-15', 'Alice'), ('Laptop', '2024-03-15', 'Bob')]

💡 Note: Both orders for Laptop were placed on the same most recent date (2024-03-15), so both are included in the result, ordered by order_id.

example_3.sql — Multiple Products

$ Input: Customers: [(1, 'Alice'), (2, 'Bob')] Orders: [(101, '2024-03-10', 1, 1), (102, '2024-03-15', 2, 2), (103, '2024-03-20', 1, 3)] Products: [(1, 'Laptop', 999), (2, 'Mouse', 25), (3, 'Keyboard', 75)]

› Output: [('Keyboard', '2024-03-20', 'Alice'), ('Laptop', '2024-03-10', 'Alice'), ('Mouse', '2024-03-15', 'Bob')]

💡 Note: Each product has one most recent order: Keyboard on 2024-03-20, Laptop on 2024-03-10, and Mouse on 2024-03-15. Results are ordered by product_name alphabetically.

Constraints

1 ≤ Number of customers ≤ 10⁴
1 ≤ Number of orders ≤ 10⁵
1 ≤ Number of products ≤ 10³
Each customer orders each product at most once per day
All dates are valid and in 'YYYY-MM-DD' format
Product names are unique and non-empty
Customer names are non-empty varchar fields

Visualization

Tap to expand

Understanding the Visualization

Collect All Receipts

Gather all order records from the database

Group by Product

Organize receipts by product type (like sorting into product bins)

Sort by Date

Within each product group, arrange by date with newest first

Pick Top Receipt

Take the most recent receipt(s) from each product pile

Generate Report

Create final report ordered by product name for easy reading

Key Takeaway

🎯 Key Insight: Window functions like RANK() allow us to efficiently identify the "best" records in each group without expensive correlated subqueries. This single-pass approach scales beautifully with large datasets and handles ties gracefully.

Asked in

a Amazon 75 G Google 60 f Meta 45 ⊞ Microsoft 40 🍎 Apple 35

The optimal solution uses window functions with RANK() to efficiently identify the most recent orders for each product. By partitioning orders by product_id and ranking them by order_date in descending order, we can filter for rank=1 rows in a single pass. This approach handles ties naturally and provides O(n log n) performance, making it ideal for large datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function (Optimal)	O(n log n)	O(n)	Use RANK() window function to identify most recent orders in a single efficient pass
Two-Pass with CTE	O(n log n)	O(k)	First pass finds max date per product, second pass joins to get matching orders
Brute Force (Correlated Subquery)	O(n²)	O(1)	For each order, check if it's the most recent for its product using a correlated subquery

Window Function (Optimal) — Algorithm Steps

Use RANK() OVER (PARTITION BY product_id ORDER BY order_date DESC) to rank orders
Join Orders with Products and Customers tables
Filter results where rank = 1 (most recent orders)
Order final results by product_name, product_id, and order_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition Data

Group orders by product_id

Sort Within Partitions

Order by order_date DESC within each group

Assign Ranks

Give rank 1 to most recent orders (handles ties)

Filter & Join

Keep rank=1 rows and join with other tables

Code -

solution.c — C

// Optimal SQL query using window functions
const char* optimalQuery = 
    "WITH RankedOrders AS ( "
    "    SELECT o.order_id, o.order_date, o.customer_id, o.product_id, "
    "           RANK() OVER (PARTITION BY o.product_id ORDER BY o.order_date DESC) as date_rank "
    "    FROM Orders o "
    ") "
    "SELECT ro.order_id, ro.order_date, c.customer_id, "
    "       c.name as customer_name, p.product_id, "
    "       p.product_name, p.price "
    "FROM RankedOrders ro "
    "JOIN Products p ON ro.product_id = p.product_id "
    "JOIN Customers c ON ro.customer_id = c.customer_id "
    "WHERE ro.date_rank = 1 "
    "ORDER BY p.product_name ASC, p.product_id ASC, ro.order_id ASC";

int executeOptimalQuery(sqlite3* db) {
    return sqlite3_exec(db, optimalQuery, callback, 0, NULL);
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass for window function calculation plus sorting for final output

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results during window function calculation

⚡ Linearithmic Space

85.2K Views

High Frequency

~25 min Avg. Time

2.3K 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

🛒 The Most Recent Orders for Each Product

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler