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

Add a status temporary table. This table should have the following columns:

id - selected from the cross_join table
month - this is an alias of first_day from the cross_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 a CASE 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.

Or sign up using:

Already have an account?