Deals Of The Week - 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