Learn
Calculating Churn
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.

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.

Instructions

1.

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;

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?