SQL - Churn Rate Project

Language Help SQL
1

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
2

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!

3

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

4

I have literally been so confused for a few days now as to what i did wrong, thank you so much I had the exact same problem

5

Still cant’ find where I’m going wrong with this part of the project. Can someone please help? Thanks in advance.

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_active_87 / sum_canceled_87 AS ‘churn_rate_87’,

1.0 * sum_active_30 / sum_canceled_30 AS ‘churn_rate_30’,

FROM status_aggregate;

6

There’s quite a lot going on there to try and spot by eye and it’s not clear what issue you actually have. I’d highly suggest making a copy of your code and then editing out chunks to make sure each individual part works as expected.

You’d want triple backticks ``` to wrap your code rather than the triple hyphen --- used. See How do I format code in my posts? for deatil.