End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer activity
Finding good customers
Summary

Instruction

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.

Exercise

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.

Stuck? Here's a hint!

Use the ship_country column from the orders table.

To extract the week, use:

DATE_PART('week', registration_date) AS week