For our calculation, we’ll need one more column on the
status temporary table:
This column will be
1 only during the month that the user cancels.
From the last exercise, the sample user had a
2016-12-03 and their
subscription_end was on
2017-02-15. Their complete status table should look like:
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.
is_canceled column to the
status temporary table. Ensure that it is equal to
1 in months containing the
Derive this column using a
CASE WHEN statement. You can use the
BETWEEN function to check if a date falls between two others.
SELECT * FROM status LIMIT 100;
at the bottom of this exercise so you can visualize the status table.