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?