SQL - Churn Rate Project

Hey everyone,

I was stuck on this for a while so I figured I would explain where I stuck since I could not find an answer on this forum. I was stuck on step 8 where we had to calculate the churn rate for the past three months. It should have been straight forward but when I when hit save/run it would just show no data. I could view the status_aggreagate table just fine but when I tried to add the simple churn rate calculation for each month for each segment it would not run. I figured out that if you do not have month as a column in the status_aggregate table you cannot sort your churn rates by month. The instructions tell you to only have four columns on step 7 but if you add month in as column one the churn rate calculates just fine and displays next to the month. I pasted my code for reference.

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
), 

cross_join AS (
  SELECT *
  FROM subscriptions
  CROSS JOIN months
),

status AS (
  SELECT 
    id, 
    first_day AS month, 
    CASE
      WHEN (subscription_start < first_day)
        AND(segment = 87) 
        AND (
        subscription_end > first_day
        OR subscription_end IS NULL
      ) THEN 1
      ELSE 0
    END as is_active_87,
    CASE
      WHEN (subscription_start < first_day)
        AND(segment = 30) 
        AND (
        subscription_end > first_day
        OR subscription_end IS NULL
      ) THEN 1
      ELSE 0
    END as is_active_30,
    CASE
      WHEN (subscription_end BETWEEN first_day AND last_day)
        AND (segment = 87) THEN 1
      ELSE 0
    END AS is_canceled_87,
    CASE
      WHEN (subscription_end BETWEEN first_day AND last_day)
        AND (segment = 30) THEN 1
      ELSE 0
    END AS is_canceled_30
  FROM cross_join
),
status_aggregate AS (
  SELECT month,
    SUM(is_active_87) AS sum_active_87,
    SUM(is_active_30) AS sum_active_30,
    SUM(is_canceled_87) AS sum_canceled_87,
    SUM(is_canceled_30) AS sum_canceled_30
  FROM status
  GROUP BY month
)

SELECT month,
  1.0 * sum_canceled_87 / sum_active_87 AS churn_rate_87,
  1.0 * sum_canceled_30 / sum_active_30 AS churn_ratee_30
FROM status_aggregate;
1 Like

i would recommend to start making a copy and then experiment with commenting out everything and see how you are building up the table.

there’s either a logical or syntax step you’re missing.

the process will also help you appreciate better how to build the large queries without proofing before they get too large!

thanks a lot for this code. I was so stuck and it was so simple following your code and it ran smoothly.

Only error i noticed is simple spelling mistake ( for anyone else who is going to copy paste and try running parts of the query). Need to remove the extra e in the end.
1.0 * sum_canceled_30 / sum_active_30 AS churn_rate’e’_30
Will be.
1.0 * sum_canceled_30 / sum_active_30 AS churn_rate_30