Write a query to find the highest-spending customer based only on delivered orders?
Interview preparation resource from Gate Smashers.
Use delivered orders only, sum each customer's delivered amounts, sort descending and return the top row. SQL: SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending FROM Customers AS c JOIN Orders AS o ON c.customer_id = o.customer_id WHERE o.status = 'Delivered' GROUP BY c.customer_id, c.customer_name ORDER BY total_spending DESC LIMIT 1;
| customer_id | customer_name | city |
|---|---|---|
| 1 | Aditi Sharma | Delhi |
| 2 | Rahul Verma | Mumbai |
| 3 | Simran Kaur | Chandigarh |
| 4 | Arjun Nair | Bengaluru |
| order_id | customer_id | amount | status |
|---|---|---|---|
| 501 | 1 | 2400 | Delivered |
| 502 | 1 | 3200 | Delivered |
| 503 | 2 | 1800 | Delivered |
| 504 | 2 | 4100 | Pending |
| 505 | 3 | 950 | Cancelled |
SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spending
FROM Customers AS c
JOIN Orders AS o ON c.customer_id = o.customer_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spending DESC
LIMIT 1;Direct query
The SQL below finds the highest-spending customer based only on delivered orders by filtering to delivered rows, aggregating per customer, ordering by the total spending in descending order, and returning the first row.
- Query: See the code block in content_blocks for the exact SQL.
Why filter before aggregation
Filtering to WHERE o.status = 'Delivered' before the GROUP BY ensures only delivered orders contribute to each customer's summed spending. This prevents pending or cancelled orders from inflating totals and yields the correct highest spender based solely on delivered orders.
- Reason: Excluding non-delivered rows before aggregation avoids incorrect totals.
Steps performed by the query
The query follows a clear sequence:
- Filter: WHERE o.status = 'Delivered' — include only delivered orders.
- Join: JOIN Customers to Orders on customer_id to access customer details.
- Aggregate: SUM(o.amount) grouped by customer_id and customer_name to compute total_spending per customer.
- Order & limit: ORDER BY total_spending DESC and LIMIT 1 to return the highest spender.
Portability and tie behavior
The supplied SQL uses LIMIT 1, which is supported by PostgreSQL, MySQL and SQLite. SQL Server uses TOP (1), while standard-style systems may use FETCH FIRST 1 ROW ONLY. Note that this query returns a single customer row when totals tie; which tied row is returned is not guaranteed unless additional tie-breaking criteria are added.
- Limit syntax: Different SQL dialects use different ways to restrict result count; choose the one appropriate for your RDBMS.