Learn
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.
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_day UNION SELECT '2017-01-01' AS first_day, '2017-01-31' AS last_day;
Instructions
1.
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.
Sign up to start coding
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.