Great! Knowing which customers are active is important, but it's equally important to understand how much revenue our customers generate. We now want to know the average order value for each weekly registration cohort. Check it out:
SELECT
DATE_PART('year', registration_date) AS year,
DATE_PART('week', registration_date) AS week,
AVG(total_amount) AS average_order_value
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);
In the query above, we need data from two tables: customers (to get registration dates) and orders (to get the total amount for each order). Then, we used AVG(total_amount) to calculate the average order value.
What was the average order value for weekly registration cohorts from 2017 for orders shipped to Germany? Show two columns: week and average_order_value, and order the results by week in descending order.
Use the ship_country column from the orders table.
To extract the week, use:
DATE_PART('week', registration_date) AS week
