Calculating Churn Rates

I don’t understand why it’s not running, what did I miss?

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 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;

@shlola,

Make sure your table has all the columns you are trying to SELECT. Have you tried…

SELECT *
FROM status_aggregate

…to see which columns are there?

1 Like

HI,

I’m stuck at point no 5 in “Calculating churn rates”

Here is the code :

status AS

(SELECT id,

first_day AS months

CASE

WHEN (subscription_start < first_day)

AND (subscription_end > first_day

OR subscription_end IS NULL)

AND (segment = 87) THEN 1

ELSE 0

END is_active_87

FROM cross_join

)

SELECT * FROM status;

When I click SAVE this does not work at all. No suggestions where the issue is - just does nothing.

Please can someone assist ?

(I am aware that there is second part to this point but even if the whole code the issue is the same)

Thanks

Damian

I forgot to add a month to the status_aggregate table. After adding it, then it was solved :slight_smile:

1 Like

Trying adding “AS” like this “End AS is_active_87”.
It should look 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,

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

Hi,

Good spot there but still does not work …

I even went further and wrote whole code from point 5 + 6 and below code still does not work. It’s strange as the point 1 through 4 were fine and all tables were working.

Here is the code through points 3 to 6.

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 months

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

)

SELECT * FROM status;

I’m really confused where the problem might be. I even went through this exercise from the beggining and nothing works

I think you may have a typo try writing “first_day AS month” ^^’

it’s not that one

BTW would it matter ? It’s only alias so spelling might not be relevant ?

Yes, months and month represent two different things :slight_smile: