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
Most Recent Three Orders - Window Function SolutionInput Tablescustomer_idname1Alice2Boborder_iddatecust_id101-081201-301511-081JOIN +ROW_NUMBER()Top 3 Orders Per Customercustomer_namecustomer_idorder_idorder_dateAlice152020-11-08Alice122020-01-30Alice112020-01-08Most Recent 3 OrdersWindow Function Groups by CustomerOrders Ranked by Date DESC Within Each Group
Understanding the Visualization
1
Join
Combine customers with orders
2
Rank
ROW_NUMBER() partitioned by customer
3
Filter
Keep only top 3 per customer
Key Takeaway
🎯 Key Insight: ROW_NUMBER() with PARTITION BY efficiently handles top-N queries per group

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