Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Customer activity
Finding good customers
Summary

Instruction

Excellent! In the previous step, we found "good customers" whose average order value is above our criteria. Now, we'd like to create a more generalized report that shows the number of good customers in each weekly registration cohort. Take a look:

WITH GoodCustomers AS (
  SELECT
    C.CustomerID,
    RegistrationDate,
    AVG(TotalAmount) AS AvgOrderValue
  FROM Customers C
  JOIN Orders O
    ON C.CustomerID = O.CustomerID
  GROUP BY C.CustomerID, RegistrationDate
  HAVING AVG(TotalAmount) > 1636
)

SELECT 
  DATEPART(Year, RegistrationDate) AS Year, 
  DATEPART(Week, RegistrationDate) AS Week,
  COUNT(*) AS GoodCustomers
FROM GoodCustomers
GROUP BY
  DATEPART(Year, RegistrationDate), 
  DATEPART(Week, RegistrationDate)
ORDER BY
  DATEPART(Year, RegistrationDate), 
  DATEPART(Week, RegistrationDate);

As you can see, we put the query from the previous step inside a CTE. In the outer query, in turn, we used the DATEPART() function to extract the year and week of each customer's registration date. We grouped the good customers into weekly cohorts and counted the number of customers in each cohort.

Exercise

The template code contains the query from the previous exercise. Use it to create a report which shows the number of good customers in quarterly registration cohorts. Display the following columns: Year, Quarter, and GoodCustomers. Order the results by year and quarter.

Stuck? Here's a hint!

To get the quarter from the registration date, use:

DATEPART(Quarter, RegistrationDate) AS Quarter