Big spenders

Medium

Question

A customer is a "big spender" when they have placed at least 3 orders with status 'completed' and the total value of those completed orders is more than 500.

Cancelled orders do not count at all (neither for the number of orders nor for the total).

Return customer (the name), completed_orders (how many completed orders) and total_spent (the sum of their amounts). The order of the rows does not matter.

group by
having
aggregate
join

Read first: Lesson 45, SQL groups: GROUP BY, HAVING and conditional counts

Tables

2 tables

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

customers3 columns · 6 rowsnot run yet

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT);

Example rows of the customers table
id	name	city
1	Asha	Pune
2	Bilal	Delhi
3	Chen	Mumbai
4	Dara	Pune
5	Esha	Chennai
6	Farid	Delhi

orders5 columns · 23 rowsnot run yet

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), amount REAL NOT NULL, status TEXT NOT NULL, ordered_at TEXT NOT NULL);

Example rows of the orders table
id	customer_id	amount	status	ordered_at
1	1	250	completed	2026-01-04
2	1	199.99	completed	2026-01-19
3	1	120.5	completed	2026-02-02
4	1	300	cancelled	2026-02-10
5	2	100	completed	2026-01-05
6	2	80.25	completed	2026-01-12
7	2	60	completed	2026-01-30
8	2	40.75	completed	2026-02-08
9	3	400	completed	2026-01-08
10	3	350	completed	2026-01-21
11	3	100	cancelled	2026-02-03
12	4	120	completed	2026-01-02
13	4	130	completed	2026-01-09
14	4	99.99	completed	2026-01-16
15	4	150.01	completed	2026-01-23
16	4	110	completed	2026-01-30
17	5	100	completed	2026-01-03
18	5	100	completed	2026-01-17
19	5	100	completed	2026-02-01
20	5	400	cancelled	2026-02-12
21	6	200	completed	2026-01-06
22	6	150	completed	2026-01-20
23	6	150	completed	2026-02-04

About this SQL

SQLite lets you use a SELECT alias in GROUP BY and HAVING. Postgres does not accept an alias in HAVING, so repeat the aggregate in HAVING if you want the same query to work everywhere. SUM of a REAL column is a floating-point number, so tiny rounding differences are expected and forgiven by the checker.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready