Find above-average customer spending
Calculate total_spent for every customer from all their orders, treating no orders or all-NULL amounts as 0. Return customer_id, name and total_spent only for customers whose total_spent is strictly above the average of these customer totals. Include zero-spend customers in that average. Row order does not matter. A subquery or CTE is fine.
Difficulty: intermediate
Show hint
First build one total per customer with LEFT JOIN and COALESCE. Then compare each total against AVG of that same set of customer totals.
Tables and sample data
Each test starts with fresh tables. Result rows may be returned in any order. Column names and column order must match.
| customer_id (integer) | name (text) | city (text) |
|---|---|---|
| 1 | Asha | Kathmandu |
| 2 | Ben | London |
| 3 | Chen | Singapore |
| order_id (integer) | customer_id (integer) | amount (numeric) | status (text) | region (text) |
|---|---|---|---|---|
| 1 | 1 | 100 | paid | East |
| 2 | 1 | 200 | paid | East |
| 3 | 2 | 60 | pending | West |
| customer_id | name | total_spent |
|---|---|---|
| 1 | Asha | 300 |