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:
SELECT * FROM status LIMIT 100;
at the bottom of this exercise so you can visualize the status table.