Good job! We already know how many churned customers there are in each registration cohort. However, we would like to compare the number of churned customers to the total number of customers in a given registration cohort. How can we do that? Let's look:
SELECT
DATE_PART('year', registration_date) AS year,
DATE_PART('week', registration_date) AS week,
COUNT(customer_id) AS all_customers,
COUNT(CASE WHEN CURRENT_DATE - last_order_date > INTERVAL '30' day THEN customer_id END) AS churned_customers,
COUNT(CASE WHEN CURRENT_DATE - last_order_date > INTERVAL '30' day THEN customer_id END) * 100.0 / COUNT(customer_id) AS churned_percentage
FROM 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 used COUNT(customer_id) to count all the customers in a given cohort. Then we used COUNT(CASE WHEN ...) to include only customers that placed an order no later than 30 days ago.
Next, we calculated the percentage of churned customers in the cohort by dividing the number of churned customers by the number of all customers. Note that we multiplied the numerator by 100.0 (of type numeric) and not 100 (of type integer) to avoid integer division.
Modify the template so that it shows the following columns: month, all_customers, churned_customers, and churned_percentage. Find the number of churned customers in monthly signup cohorts from 2017. In this exercise, churned customers are those who haven't placed an order in 45 days.
Previously, we only saw churned customer counts with no reference to total customer counts. Now we can clearly see the relation between these figures. What do you think about the churn values? Are they high?
Move the interval from the WHERE clause into a COUNT(CASE WHEN ...) clause.
