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
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:
at the bottom of this exercise so you can visualize the status table.