Learn
Calculating Churn
Multiple Month: Determine Active Status

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 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.

Folder Icon

Sign up to start coding

Already have an account?