Brightlane's VIP program team wants to recognize each customer's highest-value purchase on record. Where a customer has multiple orders tied at the same maximum amount, every tied order should appear in the result.
Write a query to return the order ID, customer ID, and total amount for every order whose total_amount equals that same customer's highest order amount.
Assumptions:
- The
orderstable has one row per order with anid, acustomer_id, and atotal_amount. - A customer's highest order amount is the largest
total_amountacross every order linked to thatcustomer_id. - An order qualifies when its
total_amountis exactly equal to that customer's highest order amount. When a customer has multiple orders tied at the highest amount, every tied order qualifies.
Output:
- One row per qualifying order, with columns
id,customer_id, andtotal_amount.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
customer_id,
total_amount
FROM
orders o
WHERE
total_amount = (
SELECT
MAX(total_amount)
FROM
orders inner_o
WHERE
inner_o.customer_id = o.customer_id
)The shape
Equality against a per-customer maximum is what makes this a correlated subquery problem and what makes it tie-safe. The outer \WHERE\ compares each order's \total_amount\ to its own customer's \MAX(total_amount)\. Every order whose amount equals that customer-specific maximum qualifies, so when a customer has two orders tied at their highest amount, both rows survive the filter.
Clause by clause
- \
SELECT id, customer_id, total_amount\returns the three columns the spec asks for, taken straight from the qualifying order row. - \
FROM orders o\reads every order and aliases the table as \o\so the inner subquery can correlate to the outer customer. - \
WHERE total_amount = (SELECT MAX(total_amount) FROM orders inner_o WHERE inner_o.customer_id = o.customer_id)\is the correlated equality filter. The inner alias \inner_o\distinguishes the inner orders rows from the outer \o\. The predicate \inner_o.customer_id = o.customer_id\ties the inner \MAX\to the same customer as the outer row, so the threshold the outer row is compared against is that customer's own maximum. The outer comparison uses \=\rather than the more familiar \>\, and that is the load-bearing choice for this problem.
The trap
The instinct on "find each customer's highest-value order" is to reach for \LIMIT 1\ or to aggregate with \MAX\ in a \GROUP BY\. Both lose ties silently. \SELECT customer_id, MAX(total_amount) FROM orders GROUP BY customer_id\ returns one row per customer with the maximum amount, but it does not return the original order rows and it does not give you a way to recover the order \id\ of every tied order. Joining that aggregate back to \orders\ on \(customer_id, total_amount)\ does recover the ties, and it is a valid alternative. The correlated subquery does the same work in one shape: it computes the per-customer maximum inside the predicate and keeps every order that matches it, ties included. The general rule is that equality against a correlated aggregate preserves ties by construction; an outer self-join on the aggregated key does the same; any "top one per group" shape that relies on \LIMIT\ will drop the ties.
You practiced WHERE column = (correlated MAX...) — equality against a per-row aggregate keeps every record tied at the maximum, the right shape when ties should all be retained.
