Brightlane's finance team is building a customer value report and needs the largest single purchase on record for each customer.
Write a query to return every customer's ID, name, and highest order amount.
Assumptions:
- A customer's highest order amount is the largest
total_amountacross orders linked to thatcustomer_id. - Every customer must appear, including customers with no orders. Customers with no orders should show a missing value in the highest-amount column.
Output:
- One row per customer, with columns
id,name, andmax_order_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
c.id,
c.name,
(
SELECT
MAX(total_amount)
FROM
orders o
WHERE
o.customer_id = c.id
) AS max_order_amount
FROM
customers cThe shape
The largest single purchase belongs to each customer row, so \MAX(total_amount)\ goes inline in the \SELECT\ list as a correlated subquery. For every customer, the inner query runs once against \orders\ filtered to that customer's \id\, and the result is attached as the third column. Customers with no orders get NULL from \MAX\ over an empty set, which is the missing value the spec asks for.
Clause by clause
- \
SELECT c.id, c.name, (SELECT MAX(total_amount) FROM orders o WHERE o.customer_id = c.id) AS max_order_amount\returns the customer's id and name, then the customer's largest order amount. The inner \WHERE o.customer_id = c.id\references the outer row's \c.id\, so each customer's \MAX\is computed against that customer's orders only. An aggregate over zero rows returns NULL, so a customer with no orders gets a missing value in \max_order_amount\. - \
FROM customers c\reads every customer. No outer \WHERE\because every customer is required in the output.
Why this and not \LEFT JOIN\ plus \GROUP BY\
A \LEFT JOIN\ from \customers\ to \orders\ with \GROUP BY c.id, c.name\ and \MAX(o.total_amount)\ produces the same result, including the NULL for customers with no orders. The correlated scalar subquery is one expression instead of three clauses and keeps the customer row intact without requiring a \GROUP BY\. When the related-table value is one scalar per outer row, the inline subquery reads more directly.
You practiced a correlated MAX(...) in the SELECT list — when the customer has no related orders, the inner aggregate operates over zero records and returns missing, propagating naturally to the outer row.
