End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Churned customers
3. Churned customers in weekly cohorts
Customer retention charts
Summary

Instruction

Perfect! We know how to count the total number of churned customers. Next, let's see how we can split that count into weekly registration cohorts. Take a look:

SELECT
  DATE_PART('year', registration_date) AS year,
  DATE_PART('week', registration_date) AS week,
  COUNT(*) As churned_customers
FROM customers
WHERE CURRENT_DATE - last_order_date > INTERVAL '30' day
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 usual, we used the DATE_PART() function to extract the week and year of users' registrations. This information is used in the GROUP BY clause to group customers into weekly signup cohorts.

Exercise

Find the number of churned customers in monthly registration cohorts from 2017. In this exercise, churned customers are those who haven't placed an order in more than 45 days. Show the following columns: month and churned_customers. Order the results by month.

Stuck? Here's a hint!

Remember to use the WHERE clause to filter customers based on registration_date.