FAQ: Calculating Churn - Single Month I

I got the answer and they still won’t let me proceed.

SELECT COUNT(*)
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
subscription_end
BETWEEN ‘2017-01-01’ AND ‘2017-01-31’
);

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

SELECT 1.0*35/277;

:grimacing:

I redid it, but I still shall not pass…

SELECT COUNT(*)
FROM subscriptions
WHERE subscription_end
BETWEEN ‘2017-01-01’ AND ‘2017-01-31’
;

SELECT COUNT(*)
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’;

SELECT 1.0 *
(SELECT COUNT()
FROM subscriptions
WHERE subscription_end
BETWEEN ‘2017-01-01’ AND ‘2017-01-31’) /(
SELECT COUNT(
)
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’);

All the following options 1,2 and 3 give the same result of ).126.
Can somebody explain the difference.

–Option 1
SELECT ROUND (1.0 *
(
SELECT COUNT()
FROM subscriptions
WHERE
subscription_end
BETWEEN ‘2017-01-01’
AND ‘2017-01-31’
) /(
SELECT COUNT(
)
FROM subscriptions
WHERE subscription_start<‘2017-01-01’

  ), 3)

AS result ;

–Option 2
SELECT ROUND (1.0 *
(
SELECT COUNT()
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
subscription_end
BETWEEN ‘2017-01-01’
AND ‘2017-01-31’
)
) / (
SELECT COUNT(
)
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
)
), 3)
AS result;

–Option 3
SELECT ROUND (1.0 *
(
SELECT COUNT()
FROM subscriptions
WHERE
subscription_end
BETWEEN ‘2017-01-01’
AND ‘2017-01-31’
) /(
SELECT COUNT(
)
FROM subscriptions
WHERE subscription_start < ‘2017-01-01’
AND (
(subscription_end >= ‘2017-01-01’)
OR (subscription_end IS NULL)
)
), 3)

AS result ;

I came here with the same question along with some other ones. And I agree with you. My guess is that by not having any cancellations on the month of interest the code was left like that. But it wouldn’t work otherwise so they should’ve made it flawless. Or else, we will not learn the correct way. Idk, that’s my guess.

Also, if anybody asks themselves why didn’t they also limit the subscription_start date/month for the cancellations instead of {WHERE subscription_start <‘2016-12-01’}. I think its because its covered in the subscription_end part of the code since its impossible for someone to cancel that month without subscribing :slight_smile:

Srry for my english, not my main language

it is included, in my opinion, since its a date, the older the bigger *. So by asking for less than (<), you will get everything starting from jan up until now. Furthermore, everything after that date (i.e.: december 2016, etc) won’t be included

Nevermind im brain dead, it’s the other way around

I found the same result and I dont know what the problem is :confused:

select 1.0 *
(
  select count(*)
  from subsriptions
  where subscription_start < '2017-01-01'     and (subscription_end between '2017-01-01'  and '2017-01-31')
) / ( 
  select count(*)
  from subscriptions
  WHERE subscription_start <= '2017-01-01')     and ((subscription_end >= '2017-01-31') or  (subscription_end is null))
)
as result;


above is my code
which looks exactly like the solution but it is not working.
whats wrong with the code???

I kept having the same problem and found the way out by adding a couple of parentheses.
OR clause limiting the cancel date for total subscribers should be calculated first, and then its result would be passed for AND clause that limits the start date for total subscribers.
So the last part of your code:

WHERE
(subscription_start < ‘2017-01-01’)
AND
(subscription_end >= ‘2017-01-01’)
OR
(subscription_end IS NULL)
)
AS Result;

should be:

WHERE
(subscription_start < ‘2017-01-01’)
AND
((subscription_end >= ‘2017-01-01’)
OR
(subscription_end IS NULL))
)
AS Result;

Maybe you could give it a try!