The previous method worked, but you may have noticed we selected the same group of customers twice for the same month and repeated a number of conditional statements.
Companies typically look at churn data over a period of many months. We need to modify the calculation a bit to make it easier to mold into a multi-month result. This is done by making use of
To start, use
WITH to create the group of customers that are active going into December:
WITH enrollments AS (SELECT * FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( (subscription_end >= '2016-12-01') OR (subscription_end IS NULL) )),
Let’s create another temporary table that contains an
is_canceled status for each of these customers . This will be 1 if they cancel in December and 0 otherwise (their cancellation date is after December or
status AS (SELECT CASE WHEN (subscription_end > '2016-12-31') OR (subscription_end IS NULL) THEN 0 ELSE 1 END as is_canceled, ...
We could just
COUNT() the rows to determine the number of users. However, to support the multiple month calculation, lets add a
is_active column to the
status temporary table. This uses the same condition we created
status AS ... CASE WHEN subscription_start < '2016-12-01' AND ( (subscription_end >= '2016-12-01') OR (subscription_end IS NULL) ) THEN 1 ELSE 0 END as is_active FROM enrollments )
This tells us if someone is active at the beginning of the month.
The last step is to do the math on the
status table to calculate the month’s churn:
SELECT 1.0 * SUM(is_canceled) / SUM(is_active) FROM status;
We make sure to multiply by
1.0 to force a float result instead of an integer.
Use the methodology provided in the narrative to calculate the churn for January 2017.
subscriptions table contains: