Learn
We now have a cross joined table that looks something like:
id | subscription_start | subscription_end | month |
---|---|---|---|
1 | 2016-12-03 | 2017-02-15 | 2016-12-01 |
1 | 2016-12-03 | 2017-02-15 | 2017-01-01 |
1 | 2016-12-03 | 2017-02-15 | 2017-02-01 |
1 | 2016-12-03 | 2017-02-15 | 2017-03-01 |
If you remember our single month example, our ultimate calculation will make use of the status
temporary table.
The first column of this table was used in the denominator of our churn calculation:
is_active
: if the subscription started before the given month and has not been canceled before the start of the given month
For the example above, this column would look like:
month | is_active |
---|---|
2016-12-01 | 0 |
2017-01-01 | 1 |
2017-02-01 | 1 |
2017-03-01 | 0 |
Instructions
1.
Add a status
temporary table. This table should have the following columns:
id
- selected from thecross_join
tablemonth
- this is an alias offirst_day
from thecross_join
table. We’re using the first day of the month to represent which month this data is for.is_active
- 0 or 1, derive this column using aCASE WHEN
statement
The is_active
column should be 1 if the subscription_start
is before the month’s first_day
and if the subscription_end
is either after the month’s first_day
or is NULL
.
We’ve added:
SELECT * FROM status LIMIT 100;
at the bottom of this exercise so you can visualize the temporary table you create.
Sign up to start coding
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.