The Most Recent Three Orders - Problem

You are tasked with building a customer order analysis system for an e-commerce platform. Given information about customers and their orders, you need to find the most recent three orders for each customer to understand their purchasing patterns.

🎯 Your Goal: Extract the 3 most recent orders for each customer. If a customer has fewer than 3 orders, return all their orders.

📊 Tables Structure:

  • Customers table: Contains customer information (customer_id, name)
  • Orders table: Contains order details (order_id, order_date, customer_id, cost)

🔧 Output Requirements:

  • Order results by customer name (ascending)
  • If customer names are tied, order by customer_id (ascending)
  • If still tied, order by order_date (descending - most recent first)

This problem simulates real-world scenarios where businesses analyze customer behavior by examining their recent purchase history to make recommendations or identify trends.

Input & Output

example_1.sql — Basic Example
$ Input: Customers table: | customer_id | name | |-------------|-------| | 1 | Alice | | 2 | Bob | | 3 | Tom | | 4 | Jerry | Orders table: | order_id | order_date | customer_id | cost | |----------|------------|-------------|----- | | 1 | 2020-07-31 | 1 | 30 | | 2 | 2020-07-30 | 2 | 40 | | 3 | 2020-07-31 | 3 | 70 | | 4 | 2020-07-29 | 4 | 100 | | 5 | 2020-06-10 | 1 | 1010 | | 6 | 2020-08-01 | 2 | 102 | | 7 | 2020-08-01 | 3 | 111 | | 8 | 2020-08-03 | 1 | 99 | | 9 | 2020-08-07 | 2 | 32 |
› Output: | customer_id | customer_name | order_id | order_date | cost | |-------------|---------------|----------|------------|----- | | 1 | Alice | 8 | 2020-08-03 | 99 | | 1 | Alice | 1 | 2020-07-31 | 30 | | 1 | Alice | 5 | 2020-06-10 | 1010 | | 2 | Bob | 9 | 2020-08-07 | 32 | | 2 | Bob | 6 | 2020-08-01 | 102 | | 2 | Bob | 2 | 2020-07-30 | 40 | | 4 | Jerry | 4 | 2020-07-29 | 100 | | 3 | Tom | 7 | 2020-08-01 | 111 | | 3 | Tom | 3 | 2020-07-31 | 70 |
💡 Note: Alice and Bob each have 3 orders, so we show their 3 most recent orders sorted by date descending. Jerry has only 1 order and Tom has 2 orders, so we show all their orders. Results are sorted by customer name (Alice, Bob, Jerry, Tom), then by order date descending within each customer.
example_2.sql — Customers with Same Name
$ Input: Customers table: | customer_id | name | |-------------|-------| | 1 | John | | 2 | John | Orders table: | order_id | order_date | customer_id | cost | |----------|------------|-------------|----- | | 1 | 2020-07-31 | 1 | 100 | | 2 | 2020-07-30 | 2 | 200 | | 3 | 2020-08-01 | 1 | 150 | | 4 | 2020-08-02 | 2 | 250 |
› Output: | customer_id | customer_name | order_id | order_date | cost | |-------------|---------------|----------|------------|----- | | 1 | John | 3 | 2020-08-01 | 150 | | 1 | John | 1 | 2020-07-31 | 100 | | 2 | John | 4 | 2020-08-02 | 250 | | 2 | John | 2 | 2020-07-30 | 200 |
💡 Note: Both customers have the same name 'John', so we sort by customer_id as the tiebreaker. Customer 1 comes before Customer 2. Within each customer, orders are sorted by date descending.
example_3.sql — Edge Case - Customer with No Orders
$ Input: Customers table: | customer_id | name | |-------------|-------| | 1 | Alice | | 2 | Bob | Orders table: | order_id | order_date | customer_id | cost | |----------|------------|-------------|----- | | 1 | 2020-07-31 | 1 | 100 | | 2 | 2020-08-01 | 1 | 150 |
› Output: | customer_id | customer_name | order_id | order_date | cost | |-------------|---------------|----------|------------|----- | | 1 | Alice | 2 | 2020-08-01 | 150 | | 1 | Alice | 1 | 2020-07-31 | 100 |
💡 Note: Alice has 2 orders (less than 3), so both are returned. Bob has no orders, so he doesn't appear in the result since we're using INNER JOIN between Customers and Orders tables.

Constraints

  • 1 ≤ customers.length ≤ 1000
  • 1 ≤ orders.length ≤ 104
  • Each customer has at most one order per day
  • All customer_ids in Orders table exist in Customers table
  • All dates are valid and in YYYY-MM-DD format

Visualization

Tap to expand
Window Function: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)1Join Tables2Partition by Customer3Assign Row Numbers4Filter Top 3Customer A2023-12-01 | Rank 1 ✓2023-11-15 | Rank 2 ✓2023-10-20 | Rank 3 ✓2023-09-01 | Rank 4 ✗Customer B2023-11-30 | Rank 1 ✓2023-11-01 | Rank 2 ✓Final Result: Top 3 orders per customer, sorted by name → customer_id → date DESCCustomer A: Order1(2023-12-01), Order2(2023-11-15), Order3(2023-10-20)Customer B: Order4(2023-11-30), Order5(2023-11-01)
Understanding the Visualization
1
Collect All Orders
Gather all customer orders with their personal information from both tables
2
Group by Customer
Organize orders into groups by customer (like sorting receipts into customer folders)
3
Rank Within Groups
Within each customer group, assign ranks based on order date (most recent = rank 1)
4
Select Top 3
From each customer group, select only the orders with ranks 1, 2, and 3
5
Final Sort
Sort the final results by customer name, then customer ID, then order date
Key Takeaway
🎯 Key Insight: Window functions like ROW_NUMBER() provide an elegant and efficient way to solve "top N per group" problems, eliminating the need for expensive correlated subqueries while leveraging database engine optimizations.

Related Problems

Asked in
Amazon 45 Google 38 Meta 32 Microsoft 28 Apple 22
47.0K Views
High Frequency
~15 min Avg. Time
1.9K 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