Question about query SQL User churn

why this line in status table mean active user? this is solution of code academy. this lesson really lack of explaination.

part 3 of lesson, link course : https://www.codecademy.com/paths/data-science/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-user-churn/lessons/churn-rates-les/exercises/single-month-ii

CASE
WHEN subscription_end >= β€˜2017-01-01’ (why this line mean active user?)
OR
subscription_end IS NULL
THEN 1
ELSE 0
END as is_active
FROM enrollments

This code gets every user subscribed at the begin of the month.
If the user cancels his subscription after the begin of the month, he still counts as an active member for churn rate.

But, if he canceled his subscription before the begin of the month, he isn’t an active member for churn rate.

1 Like

if so, please read my code with question, i see some conflict with is_canceled
please explain it for me, it would help for further learning.
full code:

WITH enrollments AS (this table select all active and cancel right? because i cannot run this select in website)
(SELECT *
FROM subscriptions
WHERE subscription_start < β€˜2017-01-01’
AND (
(subscription_end >= β€˜2017-01-01’) (canceled?)
OR (subscription_end IS NULL) (active)
)),
status AS
(SELECT
CASE
WHEN (subscription_end > β€˜2017-01-31’) / because 'THEN 0, so canceled member has subs_end <= β€˜2017-01-31’ but you said that active member can be the one cancel from the beginning β€˜2017-01-01’? conflict here.
OR (subscription_end IS NULL) THEN 0
ELSE 1
END as is_canceled,
CASE
WHEN 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;

Ok. Let me see if I can explain it to you.
This table will show you all users in January:

SELECT *
FROM subscriptions
WHERE subscription_start < β€˜2017-01-01’ #Pop-out all users that have subscribed in January
  AND (
  (subscription_end >= β€˜2017-01-01’) 
  OR (subscription_end IS NULL) 
  ) #Pop-out all users that have canceled BEFORE January

Having a list of all users that have begun January month subscribed, we can find those who canceled or not this month.
Explaining the code:

SELECT
  CASE #Pop-out all members that have canceled AFTER January
  WHEN (subscription_end > β€˜2017-01-31’) 
  OR (subscription_end IS NULL) THEN 0
  ELSE 1
  END as is_canceled,
CASE #Select all members that haven't canceled. Note that this statement SELECT because uses THEN 1 after CASE
  WHEN subscription_end >= β€˜2017-01-01’
  OR
  subscription_end IS NULL
  THEN 1
  ELSE 0
  END as is_active
FROM enrollments

Reply me if this doesn’t answer your question.