Struggling to understand why this line of code is needed

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

When calculating Churn on the second problem in the “Single Month I” section, I’m having trouble understanding why the
“OR (subscription_end >= '2017-01-01)”
is needed. If the denominator is looking for current accounts that haven’t cancelled in January, why is there a filter where the subscription HAS ended, instead of solely NULL?

@jalexmcgraw838836446 Welcome to the forums! What I think it is meaning is that only count things that haven’t ended before they started, or NULL ones, not ones that have ended before 2017-01-01. Happy coding!

2 Likes

Without a link to the exercise you’re attempting, I can’t be sure, but my intuition would be that perhaps you’re looking at historical data and not the latest figures for Jan 2017.

In which case, if you’ve got data which goes beyond Jan 2017 you don’t want to eliminate subscriptions which were cancelled in a later month from your calculation of active subscribers in January.

Your WHERE will give you any subscription which started in December 2016 or earlier, and which is either still running or was cancelled in January or later.