Well done! Now, let's analyze our e-store's "good customers." Each business will use its own definition of a good customer that is based on their business model. In our e-store, we'll define a "good customer" as a customer whose average order value is above the general average order value for all customers. Analyzing such customers may help us understand what makes customers spend more. This, in turn, can help us decide which marketing campaigns we should focus on.
First, we need to see the average order value for each individual customer:
AVG(total_amount) AS avg_order_value
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY AVG(total_amount);
The query is quite straightforward: we join the tables
orders so that we can calculate
AVG(total_amount) and get some additional information about the customer.