Find Top Customers

Easy ⏱ 8 min 82% acceptance ★★★★★ 4.7

A DataVix retail client wants to reward its highest-spending customers. Return the top 5 customers by total amount spent across all of their orders. Only count orders that were actually fulfilled (status = 'Delivered'). Output the customer name and their total spend, highest first.

Database Schema

Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.

customers 5 columns

customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE

products 4 columns

product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL

orders 5 columns

order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT

order_items 4 columns

order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL

payments 5 columns

payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE

employees 6 columns

employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE

Examples

Example 1

Input

customers, orders tables (see Schema panel)

Output

name | total_spent
Rahul | 18693
John | 18999 ...

Explanation

Sum each customer's Delivered order amounts, then take the 5 biggest spenders.

Constraints

Only include orders with status = 'Delivered'.
Return exactly 5 rows.
Order by total spend descending.

Topics

GROUP BYJOINORDER BYAggregation

Companies

AmazonFlipkartRazorpay

Hints

Hint 1

Join customers to orders on customer_id.

Hint 2

Filter status before aggregating.

Hint 3

GROUP BY the customer, SUM(amount), then ORDER BY that sum DESC and LIMIT 5.

Reveal Official Solution

Try it yourself first — you learn more from a struggle than a spoiler.

SELECT c.name, SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'Delivered'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC
LIMIT 5;

Related Problems

Customers From a City →

Press Run (or Ctrl+Enter) to execute your query against the sample database.

customers5 cols

customer_idINTEGER
nameTEXT
cityTEXT
countryTEXT
signup_dateDATE

customer_id	name	city	country	signup_date
101	Rahul	Delhi	India	2023-01-12
102	Aman	Mumbai	India	2023-02-03
103	Sara	Bengaluru	India	2023-02-20

products4 cols

product_idINTEGER
product_nameTEXT
categoryTEXT
priceREAL

product_id	product_name	category	price
1	Wireless Mouse	Electronics	799
2	Office Chair	Furniture	5499
3	Notebook	Stationery	149

orders5 cols

order_idINTEGER
customer_idINTEGER
order_dateDATE
amountREAL
statusTEXT

order_id	customer_id	order_date	amount	status
1001	101	2024-01-05	1598	Delivered
1002	102	2024-01-11	5499	Delivered
1003	101	2024-02-02	149	Cancelled

order_items4 cols

order_idINTEGER
product_idINTEGER
quantityINTEGER
unit_priceREAL

order_id	product_id	quantity	unit_price
1001	1	2	799
1002	2	1	5499
1004	3	5	149

payments5 cols

payment_idINTEGER
order_idINTEGER
methodTEXT
amountREAL
paid_dateDATE

payment_id	order_id	method	amount	paid_date
5001	1001	UPI	1598	2024-01-05
5002	1002	Card	5499	2024-01-11
5003	1005	UPI	999	2024-03-02

employees6 cols

employee_idINTEGER
nameTEXT
departmentTEXT
manager_idINTEGER
salaryREAL
hire_dateDATE

employee_id	name	department	manager_id	salary
1	Meera	Analytics	NULL	180000
2	Rohit	Analytics	1	95000
3	Neha	Engineering	1	120000

Loading expected output…