Calculating Churn Rates

My code doesn’t run and I can’t seem to figure out why

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-31' 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 (subscription_end > first_day 
      OR subscription_end IS NULL)
    AND (segment = 87) THEN 1
  ELSE 0
  END as is_active_87, 
  
  CASE WHEN (subscriptions_start < first_day)    
    AND (subscription_end > first_day
      OR subscription_end IS NULL)
    AND (segment = 30) 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
    FROM cross_join, 
  
  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_rate_30
  FROM status_aggregate;
1 Like

I made several amendments but it still doesn’t work.

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 (subscription_end > first_day 
      OR subscription_end IS NULL)
    AND (segment = 87) THEN 1
  ELSE 0
  END as is_active_87, 
  
  CASE WHEN (subscriptions_start < first_day)    
    AND (subscription_end > first_day
      OR subscription_end IS NULL)
    AND (segment = 30) 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 , 1.0*sum_canceled_30/sum_active_30
  FROM status_aggregate;

If you run this on sqlite3 or dbbrowser you’re likely to get more responsive error messages.

I think what I did when I did this was I copied the db over to my local system and then used one of those two programs. That way the debugging was easier. It may seem like a hassle, but realistically working without error messages is not the norm, and in a query this big it’s annoying not to any.

Your code looks pretty close…

You’re not going to see results from CTE unless you do a SELECT * from that temp. table.
Also, this might be useful(?):

1 Like

Also having issues with this project.

I did find a bunch of typos, but havent been able to get results from the query, and as its already been stated, not getting any sort of error message from the in browser system.

Does anyone have the code working that they could share for review?

As policy states, we don’t give out answers here.

What are the issues you’re having?
Perhaps you could post your code and we could all look through it with you?

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 (segment = 87)

      AND (

        subscription_end > first_day

        OR subscription_end IS NULL

      ) THEN 1

    ELSE 0

  END AS is_active_87,

  CASE 

    WHEN (segment = 30)

      AND (

        subscription_end > first_day

        OR subscription_end IS NULL

      ) THEN 1

    ELSE 0

  END AS is_active_30,

  CASE

    WHEN (segment = 87)

      AND (

        subscription_end BETWEEN first_day AND last_day

     ) THEN 1

    ELSE 0

  END AS is_canceled_87,

  CASE

    WHEN (segment = 30)

      AND (

        subscription_end BETWEEN first_day AND last_day

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

FROM status_aggregate;

Go back over your first CASE statement for “is_active_87” (and your CASE statement for “is_active_30”).

You’re missing a key component. you’re not accounting for (subscription_start < first_day)

I did see what you were talking about.

My issue is the code isnt returning any tables though. 8

(`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 (subscription_end > first_day
OR subscription_end IS NULL)
AND (segment = 87)
THEN 1
ELSE 0
END AS is_active_87,
CASE
WHEN (subscription_start < first_day)
AND (subscription_end > first_day
OR subscription_end IS NULL)
AND (segment = 30)
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_rate_30
FROM status_aggregate;
`

You’re not going to see results from a temp. table unless you do a SELECT *
You’re only going to see results from the 1, 2 and last question b/c you’re creating temp. tables (CTE’s) building up to the last question.

Check this out: