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

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.

Or sign up using:

Already have an account?