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 good_customers AS (
SELECT
c.customer_id,
registration_date,
AVG(total_amount) AS avg_order_value
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, registration_date
HAVING AVG(total_amount) > 1636
)

SELECT
DATE_PART('year', registration_date) AS year,
DATE_PART('week', registration_date) AS week,
COUNT(*) AS good_customers
FROM good_customers
GROUP BY DATE_PART('year', registration_date), DATE_PART('week', registration_date)
ORDER BY DATE_PART('year', registration_date), DATE_PART('week', registration_date);


As you can see, we put the query from the previous step inside a CTE. In the outer query, in turn, we used the DATE_PART() 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 that shows data about customers with orders greater than the general average (1636.622). Use it to create a report which shows the number of good customers in quarterly registration cohorts. Display the following columns: year, quarter, and good_customers. Order the results by year and quarter.

### Stuck? Here's a hint!

Create a CTE for good customers using the template. In the CTE add the registration_date column – you will need it in the outer query.