End of Summer - 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 (1905.9063). 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.