# Calculating Churn Rates

My code doesn’t run and I can’t seem to figure out why

``````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-31' 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 (segment = 87) THEN 1
ELSE 0
END as is_active_87,

CASE WHEN (subscriptions_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
FROM cross_join,

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

I made several amendments but it still doesn’t work.

``````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 (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 (subscriptions_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 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 , 1.0*sum_canceled_30/sum_active_30
FROM status_aggregate;
``````

If you run this on sqlite3 or dbbrowser you’re likely to get more responsive error messages.

I think what I did when I did this was I copied the db over to my local system and then used one of those two programs. That way the debugging was easier. It may seem like a hassle, but realistically working without error messages is not the norm, and in a query this big it’s annoying not to any.

You’re not going to see results from CTE unless you do a `SELECT *` from that temp. table.
Also, this might be useful(?):

1 Like

Also having issues with this project.

I did find a bunch of typos, but havent been able to get results from the query, and as its already been stated, not getting any sort of error message from the in browser system.

Does anyone have the code working that they could share for review?

As policy states, we don’t give out answers here.

What are the issues you’re having?
Perhaps you could post your code and we could all look through it with you?

``````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_end > first_day

OR subscription_end IS NULL

) THEN 1

ELSE 0

END AS is_active_87,

CASE

WHEN (segment = 30)

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

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

Go back over your first `CASE` statement for “is_active_87” (and your `CASE` statement for “is_active_30”).

You’re missing a key component. you’re not accounting for (subscription_start < first_day)

I did see what you were talking about.

My issue is the code isnt returning any tables though. 8

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

You’re not going to see results from a temp. table unless you do a `SELECT *`
You’re only going to see results from the 1, 2 and last question b/c you’re creating temp. tables (CTE’s) building up to the last question.

Check this out: