FAQ: Calculating Churn - Single Month II

Would anybody happen to know where I went wrong? I used the video as a guide, but for some reason the same code doesn’t come back with any results:

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
) SELECT * FROM subscriptions;

I used the different approach to solve this problem.
Churn Rate is number of user canceled the subscription divided by total number of user.

Now to find total number of user for Jan month user need to be fulfilled :

  1. It need to subscribe before 1st Jan 2017.
  2. The subscription if cancelled then need to be cancelled after 31 Dec 2016 otherwise not cancelled.
    Now we are calling this table as enrollment table so all the member in the enrollment tables are active users.

To find the user who cancelled the subscription, he needs to cancelled the subscription before 31 Jan 2017.

so my query is like that

WITH enrollment as

(

select * from subscriptions

where subscription_start<‘2017-01-01’

and

(subscription_end >=‘2017-01-01’ or subscription_end is null)

),

status as

(

SELECT

CASE

WHEN (subscription_end <= ‘2017-01-31’)

THEN 1

ELSE 0

END as is_canceled,1 as is_active /*,subscription_start,subscription_end */

from enrollment

)

select 1.0*sum(is_canceled)/sum(is_active) from status;

I think is because is using the >= for january 01. So as far as i understand the exercise is being applied just for the month of December, so if a subscription ended beyond january first the user is still active trough all the month of december, thats why its lumped together with the conditional “OR subscription is null”

I don’t really get why the first WITH enrollments is needed. Here is what I wrote and made sense, as a reference for you:

WITH status AS (

SELECT

CASE

WHEN (subscription_end BETWEEN ‘2017-01-01’ AND ‘2017-01-31’) THEN 1

ELSE 0

END AS ‘is_canceled’,

CASE

WHEN (

(subscription_start < '2017-01-01')

AND ((subscription_end > '2016-12-31')

OR (subscription_end IS NULL))

) THEN 1

ELSE 0

END AS ‘is_active’

FROM subscriptions

)

SELECT 1.0 * SUM (is_canceled) / SUM (is_active)

AS churn_rate

FROM status;

I’m sorry if I sound dumb, but what’s the difference between Single month I(previous lesson) code and single month II code ? they both return the churn every month. sorry if I have hard time to comprehend the lesson.

Nice! This shows that you can implicitly use the WITH clause within the FROM clause by basically declaring a temporary table with the SELECT statement. I think this is because SQL does not iterate code sequentially but compiles it and all the relations before running it. It must make a table called before running the code just as a WITH clause would explicitly do that.

Here’s how I solved it with a JOIN statement and removing the second CASE statement so that you aren’t repeating the code that’s already written to create . I’m not sure if there’s any downside to this code or which one I like “better.”

WITH enrollments AS

(SELECT *
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
)),

status AS
(SELECT enrollments.id,
CASE
WHEN (subscription_end > ‘2017-01-31’)
OR (subscription_end IS NULL) THEN 0
ELSE 1
END as is_canceled
FROM enrollments
)

SELECT 1.0 * SUM(status.is_canceled) / COUNT(enrollments.id)
FROM status
JOIN enrollments
ON status.id = enrollments.id;

WITH status AS
(SELECT
CASE
WHEN (subscription_end BETWEEN ‘2017-01-01’
AND ‘2017-01-31’) THEN 1
ELSE 0
END AS is_canceled,
CASE
WHEN subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
) THEN 1
ELSE 0
END AS is_active
FROM subscriptions
)

SELECT 1.0*SUM(is_canceled)/SUM(is_active)
FROM status;

My query is not returning anything - a log to troubleshoot errors would be nice… anyone see something I cannot? I appreciate any and all help, community!

WITH status AS
(
  SELECT
  CASE
    WHEN ((subscription_end > '2017-01-31')
      OR (subscription_end IS NULL)) THEN 0
      ELSE 1
  END as is_canceled,
  CASE
    WHEN (subscription_start < '2017-01-01')
      AND (
        (subscription_end >= '2017-01-01')
        OR (subscription_end IS NULL)
      ) THEN 1
    ELSE 0
  END as in_active
  FROM subscriptions;
)
SELECT 1.0 * SUM(is_canceled) / SUM(is_active)
FROM status;

Help!!! I dont understand the problem here. it’s coming out blank.

with enrollments as ( select (*)

from subscriptions

where subscription_start < ‘2017-01-01’

and (subscription_end >‘2017-01-01’)

or (subscription_end is NULL)

),

status as

(select

case

when (subscription_end > '2017-01-31')

or (subscription_end is null) 

then 0

else 1

end as is_canceled),

status as

(select case

when subscription_start < ‘2017-01-01’

and (subscription_end >= ‘2017-01-01’)

or (subscription_end is null)

then 1

else 0

end as is_active

from enrollments),

select 1.0 * sum(is_canceled) / sum(is_active)

from status;

I used this solution to get a response but, much like yourself, I am still not sure why mine came out blank. Good luck my friend!

You will not see the results of a CTE (WITH) because it’s a temp table.
You can break up that lengthy query into smaller parts.
Try SELECT *
in order to see results. (I believe that’s in the video)

thanks, perfectly explained, I had the same question as Murilolui. The thing is that there are no user in the table that abandoned before january because of the “one month of commitment” so if you remove the AND / OR statements you also get the correct Churn Ratio.