EasySubqueries & EXISTS
Find customers who spent more than average customer
The query
SQL
SELECT c.name, c.country, total_spent.amount
FROM customers c
JOIN (
SELECT cust_id, SUM(total) AS amount
FROM orders WHERE status = 'completed'
GROUP BY cust_id
) total_spent ON c.cust_id = total_spent.cust_id
WHERE total_spent.amount > (
SELECT AVG(spent)
FROM (
SELECT SUM(total) AS spent
FROM orders WHERE status = 'completed'
GROUP BY cust_id
) avg_sub
)
ORDER BY total_spent.amount DESC;Note
“Multi-level subquery: inner calculates per-customer totals, outer computes average of those totals.