CALCULATING CHURN Multiple Month:

Multiple Month: Churn Rate Calculation Now comes the moment we've been waiting for - the actual churn rate. We use the number of canceled and active subscriptions to calculate churn for each month: churn_rate = canceled / active

Add a SELECT statement to calculate the churn rate. The result should contain two columns: month - selected from status_aggregate churn_rate - calculated from status_aggregate.canceled and status_aggregate.active.

This provides a list of months, with their corresponding number of active users at the beginning of the month and the number of those users who cancel during the month.

Add a status_aggregate temporary table. This table should have the following columns: month - selected from the status table active - the SUM() of active users for this month canceled - the SUM() of canceled users for this month We've added: SELECT *FROM status_aggregate;

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.

CASE WHEN subscription_end BETWEEN first_day AND last_day THEN 1 ELSE 0 END as is_canceled

Multiple Month: Determine Cancellation Status

For our calculation, we'll need one more column on the status temporary table: is_canceled This column will be 1 only during the month that the user cancels.

From the last exercise, the sample user had a subscription_start on 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.

SELECT month, 1.0 * canceled/active AS churn_rate FROM status_aggregate;

SELECT month, 1.0 * canceled/active AS churn_rate FROM status_aggregate;

Our churn calculation uses the first day as a cutoff for subscribers and the last day as a cutoff for cancellations. This table can be created like:

SELECT '2016-12-01' AS first_day, '2016-12-31' AS last_dayUNIONSELECT '2017-01-01' AS first_day, '2017-01-31' AS last_day;

Our single month calculation is now in a form that we can extend to a multiple month result. But first, we need months!

Some SQL table schemes will contain a prebuilt table of months. Ours doesn't, so we'll need to build it using UNION. We'll need the first and last day of each month.

Now that we have a table of months, we will join it to the subscriptions table. This will result in a table containing every combination of month and subscription.Ultimately, this table will be used to determine the status of each subscription in each month.

The workspace contains the months temporary table from the previous exercise. Create a cross_join temporary table that is a CROSS JOIN of subscriptions and months.

We will be using the months as a temporary table (using WITH) in the churn calculation. Create the months temporary table using WITH and SELECT everything from it so that you can see the structure. We need a table for January, February, and March of 2017.

WITH months AS( SELECT '2017-01-01' as first_day, '2017-01-31' as last_day UNION SELECT '2017-02-01' as first_day, '2017-02-28' as last_day UNION SELECT '2017-03-01' as first_day, '2017-03-31' as last_day ) SELECT * FROM months;

Multiple Month: Determine Active Status

We now have a cross joined table that looks something like:

Multiple Month: Sum Active and Canceled Users Now that we have an active and canceled status for each subscription for each month, we can aggregate them.

We will GROUP BY month and create a SUM() of the two columns from the status table, is_active and is_canceled.

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