We now have a cross joined table that looks something like:
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:
status temporary table. This table should have the following columns:
id- selected from the
month- this is an alias of
cross_jointable. 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
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
SELECT * FROM status LIMIT 100;
at the bottom of this exercise so you can visualize the temporary table you create.