Customers Who Spend More Than Average in Python

An interviewer asks: which customers spend more than the average customer? It takes two levels. First you need one total per customer, then the average of those totals. Each customer counts once in that average, however many orders they placed.

Name a query with WITH: a CTE, WITH name AS ( … ), gives a query a name, and the rest of the statement reads it like a table. Because it has a name, you can read it twice: once for the rows, and once in a subquery that turns it into one number. Here it is on another question, the customers with more orders than average:

WITH counts AS (
    SELECT customer_id, COUNT(*) AS n
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, n
FROM counts
WHERE n > (SELECT AVG(n) FROM counts)

In the shop, the customers' totals (orders that weren't cancelled) are Ana Pop 75, Ben Ortiz 29, Chloe Ng 89 and Dan Weber 73. Their average is 66.5, so your script must print:

name | total
Chloe Ng | 89
Ana Pop | 75
Dan Weber | 73
(3 rows)

Your task: the starter has db and the frame of WITH spend AS ( … ): it joins orders, customers and order_items and leaves out cancelled orders. Fill in spend's SELECT (the name and SUM(quantity * unit_price) as total) and its GROUP BY customers.id. Then, after the bracket, read name, total from spend, keep the totals above the average of spend's totals, and list the biggest spender first.

Assignment
Follow the Coding Tutorial to build the query one clause per line: the inside of spend first, then the main query that reads it.

Hint
Group by customers.id, not the name: two customers can share a name. The average goes in a subquery that reads spend again; it gives one number, 66.5 here.