Top 2 Orders per Customer by Value
For each customer, find their top 2 highest-value orders — show the order details and the rank of each order within that customer's history.
- CTEs
- Window functions
- Joins
- Subqueries
- Filtering
Challenge brief
Understand the request
Retention Analytics wants to understand each customer's highest-value purchase moments to identify upsell opportunities.
Find each customer's top 2 highest-value orders using a CTE and window function.
Return
- customer_name (full name)
- order_id
- order_date
- order_amount
- order_rank (1 = highest value for that customer)
Constraints
- Return at most two orders per customer
- Break equal order values by the lower order ID
- Order customers by name and each customer's orders by rank
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATETIMEtotal_amountREAL
customers
customer_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
The technique for top-N per group is: (1) use a window function inside a CTE to rank items within each group, (2) filter the outer query to WHERE rank <= N. Here the group is customer, the item is order, and N is 2.
Hint 2
Assign ROW_NUMBER within each customer, sorting by amount descending and order ID ascending so equal values still produce exactly two positions.
Hint 3
Scaffold: WITH customer_order_ranks AS (... ROW_NUMBER() OVER (PARTITION BY customer ORDER BY amount DESC, order ID) ...) filter the outer result to positions 1 and 2.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH customer_order_ranks AS (SELECT o.order_id, c.first_name || ' ' || c.last_name AS customer_name, o.order_date, o.total_amount AS order_amount, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.total_amount DESC, o.order_id ASC) AS order_rank FROM orders o INNER JOIN customers c ON o.customer_id = c.customer_id) SELECT customer_name, order_id, order_date, order_amount, order_rank FROM customer_order_ranks WHERE order_rank <= 2 ORDER BY customer_name, order_rank;Why this works
PARTITION BY customer_id resets the row number for each customer. Ordering by amount and then order ID makes ties deterministic, so each customer returns at most two orders. Customers with only one order still contribute their single qualifying row.
Success check
8 rows — Alice and Bob each have 2 orders shown; Carol, David, Emma, Frank each have only 1 order so 1 row each
Expected result
Use this output to verify values, aliases, ordering, and row count.
| customer_name | order_id | order_date | order_amount | order_rank |
|---|---|---|---|---|
| Alice Johnson | 5001 | 2024-01-15 10:30:00 | 142.97 | 1 |
| Alice Johnson | 5009 | 2024-02-10 14:00:00 | 89.99 | 2 |
| Bob Smith | 5006 | 2024-02-01 13:30:00 | 169.97 | 1 |
| Bob Smith | 5010 | 2024-02-15 11:30:00 | 124.99 | 2 |
| Carol White | 5004 | 2024-01-22 16:45:00 | 104.98 | 1 |
| David Brown | 5005 | 2024-01-25 11:00:00 | 34.99 | 1 |
| Emma Davis | 5007 | 2024-02-03 10:20:00 | 39.99 | 1 |
| Frank Miller | 5008 | 2024-02-05 15:10:00 | 84.98 | 1 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Explore related company challenges
Airbnb
Independent Airbnb-style marketplace, booking, listing, payment, review, and guest analytics SQL practice.
Uber
Independent Uber-style mobility marketplace SQL practice covering trips, drivers, riders, pricing, payments, and promotions.
Microsoft
Independent Microsoft-style cloud, productivity, subscription, usage, support, and customer analytics SQL practice.
Return to the complete interview preparation experience.