Churning Prob

Hey guys, can anyone see what is wrong with code here? It is there anyone who can see why my query isn’t giving any results?

My Code:

With months As

(Select

‘2017-01-01’ as first_day,

‘2017-01-31’ as last_day

Union

Select

‘2017-02-01’ 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 (segement = 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 (segement = 30) Then 1

Else 0

End As is_active_30,

Case

When (subscription_end Between first_day And last_day) And (segement = 87) Then 1

Else 0

End As is_canceled_87,

Case

When (subscription_end Between first_day And last_day) And (segement = 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;

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

This topic was automatically closed 41 days after the last reply. New replies are no longer allowed.