Now that we’ve gone over what churn is, let’s see how we can calculate it using SQL. In this example, we’ll calculate churn for the month of December 2016.
Typically, there will be data in a subscriptions
table available in the following format:
id
- the customer idsubscription_start
- the subscribe datesubscription_end
- the cancel date
When customers have a NULL
value for their subscription_end
, that’s a good thing. It means they haven’t canceled!
Remember from the previous exercise that churn rate is:

For the numerator, we only want the portion of the customers who cancelled during December:
SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( subscription_end BETWEEN '2016-12-01' AND '2016-12-31' );
For the denominator, we only want to be considering customers who were active at the beginning of December:
SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( (subscription_end >= '2016-12-01') OR (subscription_end IS NULL) );
You might’ve noticed there are quite a few parentheses in these two queries.
When there are multiple conditions in a WHERE
clause using AND
and OR
, it’s the best practice to always use the parentheses to enforce the order of execution. It reduces confusion and will make the code easier to understand. The condition within the brackets/parenthesis will always be executed first.
Anyways, now that we have the users who canceled during December, and total subscribers, let’s divide the two to get the churn rate.
When dividing, we need to be sure to multiply by 1.0
to cast the result as a float:
SELECT 1.0 * ( SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( subscription_end BETWEEN '2016-12-01' AND '2016-12-31' ) ) / ( SELECT COUNT(*) FROM subscriptions WHERE subscription_start < '2016-12-01' AND ( (subscription_end >= '2016-12-01') OR (subscription_end IS NULL) ) ) AS result;
Here, we have the numerator divided by the denominator, and then multiplying the answer by 1.0
. At the very end, we are renaming the final answer to result
using AS
.
Instructions
We’ve imported 4 months of data for a company from when they began selling subscriptions. This company has a minimum commitment of 1 month, so there are no cancellations in the first month.
The subscriptions
table contains:
id
subscription_start
subscription_end
Use the methodology provided in the narrative to calculate the churn for January 2017.