Good job! The queries we've written so far all returned a single value. Let's change that.
Suppose we want to find the average order value for each customer from Canada. How can we do that? Let's find out.
WITH order_total_prices AS (
SUM(unit_price * quantity) AS total_price
FROM orders o
JOIN order_items oi
ON o.order_id = oi.order_id
GROUP BY o.order_id, o.customer_id
AVG(total_price) AS avg_total_price
FROM order_total_prices OTP
JOIN customers c
ON OTP.customer_id = c.customer_id
WHERE c.country = 'Canada'
GROUP BY c.customer_id, c.company_name;
In the query above, we first write a CTE that calculates the total price (before discount) for each order. Note that we also put the
customer_id column in the
SELECT clause so that we can refer to it in the outer query.
In the outer query, we use the
AVG() function as before, but this time, we also group all rows by
customer_id. We also join the CTE with another table (
customers) so that we can show company names and select only those customers who come from Canada.