SELECT c.id AS customer_id, c.name AS customer_name, c.city, COUNT(o.id) AS total_orders, ROUND(SUM(o.amount), 2) AS total_spent, ROUND(AVG(o.amount), 2) AS avg_order_amount FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.status != 'cancelled' AND o.order_date >= CURRENT_DATE - INTERVAL '90 days' GROUP BY c.id, c.name, c.city ORDER BY total_spent DESC LIMIT 10;
Write a query for PostgreSQL. Tables: customers(id, name, city), orders(id, customer_id, order_date, amount, status). List the 10 customers who spent the most in the last 90 days, with their order count and average order amount. Leave out orders whose status is "cancelled". Explain the query line by line.
