IMHO - as I’m learning this just as much as anybody-
@java7682825630 is right on. The enrollments table prepares the field by culling subscriptions that ended in previous months – effectively taking them OFF the board. That’s a necessary condition for the is_canceled clause to work.
We’re looking at churn in January. Here is the codecademy clause:
WHEN (subscription_end > ‘2017-01-31’)
OR (subscription_end IS NULL) THEN 0
ELSE 1
END as is_canceled,
Without the enrollments table acting as a filter, a subscription that ended 2016-10-20 (random plug-in example) would be counted, pass through ELSE, and be assigned 1 – becoming part of our churn calculation, against our wishes. The enrollments clause cut off these sneaky buggers by earlier including (subscription_end >= ‘2016-12-01’) OR (subscription_end IS NULL) as a WHERE condition.
But @softpower is also right – there is no reason why the is_canceled clause cannot do this locally , without the “preparatory help” of the enrollments table. Consider:
CASE
WHEN (subscription_end BETWEEN ‘2017-01-01’ AND ‘2017-01-31’)
THEN 1
ELSE 0
END as is_canceled,
I also like this better because I just favor functions that calculate to 1 ELSE 0, rather than the inverse…it’s easier for humans to think about what they want and not everything else in the world they don’t want but maybe that’s just me.
The enrollments table does end up looking pretty redundant — but to do away with it you would need to make sure your is_canceled clause supplies the necessary “filter.”
By now you’re probably thinking "Aha, but I deleted the enrollments table, hooked up the status AS clause to take input FROM subscriptions directly, and got the same correct answer. So where are these “early subscription endings” that were supposed to sabotage our result?
TO which I say - you just got lucky. The subscriptions table HAS NO subscription_end prior to ‘2017-01-01.’ Try and see for yourself:
SELECT MIN(subscription_end) FROM subscriptions;
A different data set (one with earlier subscription endings) would not be so forgiving.