Best April deals - 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 
  DATEPART(Year, RegistrationDate) AS Year, 
  DATEPART(Week, RegistrationDate) AS Week, 
  AVG(TotalAmount) AS AverageOrderValue
FROM Customers C
JOIN Orders O
  ON C.CustomerID = O.CustomerID
GROUP BY
  DATEPART(Year, RegistrationDate), 
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate), 
  DATEPART(Week, RegistrationDate);

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(TotalAmount) 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 AverageOrderValue, and order the results by week.

Stuck? Here's a hint!

Use the ShipCountry column from the Orders table.

To extract the week, use:

DATEPART(Week, RegistrationDate) AS Week