First work out each customer's total spend: the sum of total_amount over all their
orders. Then, for each city (from customers), show avg_customer_spend: the average of those
totals for the customers who live there and have ordered, rounded to 2 decimal places.
End your query with ORDER BY avg_customer_spend DESC, city.
| customers |
|---|
| customer_id (PK) |
| full_name |
| city |
| orders |
|---|
| order_id (PK) |
| customer_id (FK) |
| total_amount |
First work out each customer's total spend: the sum of total_amount over all their
orders. Then, for each city (from customers), show avg_customer_spend: the average of those
totals for the customers who live there and have ordered, rounded to 2 decimal places.
End your query with ORDER BY avg_customer_spend DESC, city.
| customers |
|---|
| customer_id (PK) |
| full_name |
| city |
| orders |
|---|
| order_id (PK) |
| customer_id (FK) |
| total_amount |
| city | avg_customer_spend |
|---|---|
| Kolkata | 178039.40 |
| Goa | 177466.85 |
| Bengaluru | 165363.48 |
| Jaipur | 164245.00 |
| Delhi | 163032.67 |
| Lucknow | 158884.53 |
| Pune | 147689.03 |
| Mumbai | 134181.70 |
| Varanasi | 134054.80 |
| Kochi | 120321.83 |
| Patna | 115859.50 |
| Chennai | 113462.37 |
| Hyderabad | 107526.38 |
| Bhopal | 99641.55 |
| Ahmedabad | 99166.90 |
Run query to see output