Skip to Content
Learn
Calculating Churn
Single Month II

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 WITH and CASE.

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 NULL).

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 enrollments with:

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.

Instructions

1.

Use the methodology provided in the narrative to calculate the churn for January 2017.

The subscriptions table contains:

  • id
  • subscription_start
  • subscription_end
Folder Icon

Sign up to start coding

Already have an account?