The Most Recent Three Orders - Problem

You are given two tables: Customers and Orders. The Customers table contains customer information, while the Orders table contains order details with dates and costs.

Your task: Find the most recent three orders for each customer. If a customer has fewer than three orders, return all of their orders.

Result requirements:

  • Order by customer name (ascending)
  • If names are tied, order by customer_id (ascending)
  • If still tied, order by order_date (descending)

Table Schema

Customers
Column Name Type Description
customer_id PK int Unique customer identifier
name varchar Customer name
Primary Key: customer_id
Orders
Column Name Type Description
order_id PK int Unique order identifier
order_date date Date when order was placed
customer_id int Foreign key to Customers table
cost int Order cost in cents
Primary Key: order_id

Input & Output

Example 1 — Multiple Customers with Different Order Counts
Input Tables:
Customers
customer_id name
1 Alice
2 Bob
3 Tom
Orders
order_id order_date customer_id cost
1 2020-01-08 1 100
2 2020-01-30 1 150
3 2020-07-31 1 200
4 2020-07-30 2 120
5 2020-11-08 1 300
6 2020-12-08 2 80
7 2020-01-01 3 70
Output:
customer_name customer_id order_id order_date
Alice 1 5 2020-11-08
Alice 1 3 2020-07-31
Alice 1 2 2020-01-30
Bob 2 6 2020-12-08
Bob 2 4 2020-07-30
Tom 3 7 2020-01-01
💡 Note:

Alice has 4 orders, so we return her 3 most recent ones (2020-11-08, 2020-07-31, 2020-01-30). Bob has 2 orders, so we return both. Tom has only 1 order, so we return that single order.
Example 2 — Customer with No Orders
Input Tables:
Customers
customer_id name
1 Alice
2 Bob
Orders
order_id order_date customer_id cost
1 2020-06-10 1 100
2 2020-08-01 1 150
Output:
customer_name customer_id order_id order_date
Alice 1 2 2020-08-01
Alice 1 1 2020-06-10
💡 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 use INNER JOIN.

Constraints

  • 1 ≤ Customers.customer_id ≤ 1000
  • 1 ≤ Orders.order_id ≤ 1000
  • Each customer has at most one order per day
  • order_date is in format YYYY-MM-DD

Visualization

Tap to expand
The Most Recent Three Orders INPUT TABLES Customers id name 1 Alice 2 Bob 3 Charlie Orders id cust_id order_date 1 1 2023-01-01 2 1 2023-02-15 3 1 2023-03-20 4 1 2023-04-10 5 2 2023-01-05 6 2 2023-02-20 7 3 2023-03-01 Multiple orders per customer ALGORITHM STEPS 1 JOIN Tables Customers JOIN Orders ON customer_id 2 RANK Orders ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY date DESC) 3 FILTER Top 3 WHERE row_num <= 3 Keep recent orders only 4 ORDER Results BY name ASC, customer_id ASC, order_date DESC WITH ranked AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY customer_id...) AS rn WHERE rn <= 3 FINAL RESULT name cust_id order_date Alice 1 2023-04-10 Alice 1 2023-03-20 Alice 1 2023-02-15 Bob 2 2023-02-20 Bob 2 2023-01-05 Charlie 3 2023-03-01 Results Summary - Alice: 3 orders (had 4) - Bob: 2 orders (all) - Charlie: 1 order (all) Total: 6 rows returned OK Sorted by name, id, date DESC Key Insight: Use ROW_NUMBER() with PARTITION BY to rank orders within each customer group. This window function assigns sequential numbers (1, 2, 3...) to each order per customer, ordered by date descending. Filter WHERE rank <= 3 keeps only the 3 most recent. Customers with fewer than 3 orders naturally return all their orders. TutorialsPoint - The Most Recent Three Orders | Optimal Solution (Window Function)

Related Problems

Asked in
Amazon 23 Facebook 18 Google 15
28.3K Views
Medium Frequency
~12 min Avg. Time
847 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