Calculating Churn Rates Project (Code Review/Help)

Get Help SQL
,
#1

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;

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/projects/calc-churn-proj

#2

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