Question: Subscription Retention
"Write a query that tracks how many users continue their subscriptions 30, 60, and 90 days after joining Netflix."

You have a table called subscriptions with:

• user_id
• subscription_start_date (the day they joined)
• subscription_end_date (the day they canceled; NULL if still active)

You need to:

1. Track how many users are still active at:
   • 30 days after joining
   • 60 days after joining
   • 90 days after joining

Sample Data (subscriptions):

Step-by-Step Explanation:

Step 1: Understand How to Check if a User is Still Subscribed

A user is considered active at X days if:

• subscription_end_date is NULL (still active)
• OR subscription_end_date is after (subscription_start_date + X days)

Example:

• If someone started on Jan 1, we check if their end date is after Jan 31 for 30-day retention.

Step 2: Create Conditions for 30, 60, and 90 Days

You need to:

• Add 30, 60, and 90 days to subscription_start_date
• Compare with subscription_end_date

SQL Query:

SELECT
  COUNT(CASE WHEN subscription_end_date IS NULL 
                 OR subscription_end_date > DATE_ADD(subscription_start_date, INTERVAL 30 DAY)
             THEN 1 END) AS active_after_30_days,
             
  COUNT(CASE WHEN subscription_end_date IS NULL 
                 OR subscription_end_date > DATE_ADD(subscription_start_date, INTERVAL 60 DAY)
             THEN 1 END) AS active_after_60_days,
             
  COUNT(CASE WHEN subscription_end_date IS NULL 
                 OR subscription_end_date > DATE_ADD(subscription_start_date, INTERVAL 90 DAY)
             THEN 1 END) AS active_after_90_days
FROM
  subscriptions;

Explanation of SQL:

• subscription_end_date IS NULL means user is still subscribed.
• OR subscription_end_date > subscription_start_date + X days means user lasted past X days.
• COUNT(CASE WHEN condition THEN 1 END) counts how many users satisfied the condition.

Expected Output:

Explanation (with sample data):

• User 1: stayed past 90 days → counted in all
• User 2: stayed only ~25 days → counted for 30 but not 60, 90
• User 3: still active (NULL end date) → counted in all
• User 4: ended early (~24 days) → not counted
• User 5: still active → counted in all

Important Points:

• DATE_ADD(subscription_start_date, INTERVAL X DAY) adds days properly.
• We use NULL end date to mean "still active."
• Users who ended early will not be counted for longer retention windows.
• Always check what happens if someone cancels exactly on the 30th, 60th, or 90th day — if exact day is allowed or not (interviewers might ask this). In this query, it requires after 30 days.