# Calculating Churn Rates Project (Code Review/Help)

Trying to complete the Calculating Churn Rates project as part of the Analyze Data with SQL Skill Path. Not sure what’s wrong with my code. Maybe a syntax error that I can’t find?

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)
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_start < first_day)
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
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;

double check the construction of the first `CASE` statement (and the others).

Specifically, should “(segment =87)” be in that spot?

should be,

``````CASE WHEN (condition1)
AND (condition2 including the OR part)

AND (3rd condition) THEN 1
ELSE 0
END AS ______,

etc

``````
``````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,
``````
1 Like