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.
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.
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.
