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.