Project--Calculating Churn Rates

Hi there,
Is here someone who has the tim to check my code. I can’t see were the fault in my code is.
Thank in advanced!

"
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_start < first_day)
THEN 1
ELSE 0
END AS is_active_87,
CASE
WHEN (segment = 30)
AND (subscription_start < first_day)
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_active_30
),
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_is_canceled_87/sum_is_active_87 AS churn_rate_87,
1.0 * sum_is_canceled_30/sum_is_active_30 AS churn_rate_30
FROM status_aggregate;
"

Double check the way you’ve constructed your CASE statements as they’re a little out of order regarding the conditions. The conditions should be the start and end dates first, then you’re checking for NULLs and the last part is the segment = ___ part

You want something like this:

... 
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,
etc.
1 Like

thank you for your reaction. I’ve changed all the condition but still not running the code :sleepy:

Shouldn’t this particular CASE statement be "is_canceled)30?

Sometimes it’s helpful to run smaller bits of code (while commenting out the rest) and see what the results are, then add in other pieces of code…to see what runs and doesn’t.

Hello,

This was some of the things that was indeed wrong in my query. I think the problem was with the conditions of my case when statement as you adressed earlier.

Thank you for your help I finally got my query running :muscle:

1 Like