Calculating Churn
Multiple Month: Determine Cancellation Status

For our calculation, we’ll need one more column on the status temporary table: is_canceled

This column will be 1 only during the month that the user cancels.

From the last exercise, the sample user had a subscription_start on 2016-12-03 and their subscription_end was on 2017-02-15. Their complete status table should look like:

month is_active is_canceled
2016-12-01 0 0
2017-01-01 1 0
2017-02-01 1 1
2017-03-01 0 0

In our examples, our company has a minimum subscription duration of one month. This means that the subscription_start always falls before the beginning of the month that contains their subscription_end. Outside of our examples, this is not always the case, and you may need to account for customers canceling within the same month that they subscribe.



Add an is_canceled column to the status temporary table. Ensure that it is equal to 1 in months containing the subscription_end and 0 otherwise.

Derive this column using a CASE WHEN statement. You can use the BETWEEN function to check if a date falls between two others.

We’ve added:

SELECT * FROM status LIMIT 100;

at the bottom of this exercise so you can visualize the status table.

Folder Icon

Sign up to start coding

Already have an account?