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;
Tested against PostgreSQL 16

Note

Multi-level subquery: inner calculates per-customer totals, outer computes average of those totals.

Tables referenced