Introduction
Churned customers
Customer retention charts
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.
