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`