Learn
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.

Instructions

1.

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?