Monthly Churn Rate by Subscription Tier

Decomposition

• Core metric: Monthly churn rate (percentage of users who churned)
• Dimensions: Subscription tier
• Time window: Monthly (calculate for each month)
• Edge cases: Exclude trial users who never activated (no activation event or flag)

Data Needs

Assumed Schema

users: user_id, signup_date, is_activated (boolean)
subscriptions: subscription_id, user_id, tier, start_date, end_date (NULL if active), is_trial (boolean)

Assumptions:

• is_activated = TRUE means the user completed activation (not just signed up for trial)
• end_date IS NOT NULL indicates a churned subscription
• Trial users who never activated have is_activated = FALSE
• One subscription per user (or we consider the most recent)

Join Logic

• INNER JOIN between subscriptions and users to ensure we only analyze users with valid activation status
• Filter out trial users who never activated using WHERE users.is_activated = TRUE

Filters and Conditions

Key filters:

• Exclude trial users: is_trial = FALSE OR (is_trial = TRUE AND users.is_activated = TRUE)
• Time-based: Generate monthly cohorts
• Churn definition: Subscription ended during the month
• Active at start: Subscription was active at the beginning of the month

Churn rate formula:

Churn Rate = (Users who churned in month) / (Users active at start of month) * 100

SQL Query

WITH monthly_periods AS (
  -- Generate list of months to analyze
  SELECT 
    DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months') + (n || ' months')::INTERVAL AS month_start
  FROM generate_series(0, 12) AS n
),

activated_users AS (
  -- Filter to only users who activated (exclude trial-only users)
  SELECT 
    u.user_id,
    u.is_activated
  FROM users u
  WHERE u.is_activated = TRUE
),

subscription_status AS (
  -- Get subscription tier and status for activated users
  SELECT 
    s.user_id,
    s.tier AS subscription_tier,
    s.start_date,
    s.end_date,
    mp.month_start,
    mp.month_start + INTERVAL '1 month' AS month_end,
    -- User was active at start of month if subscription started before month_end AND (no end_date OR ended after month_start)
    CASE 
      WHEN s.start_date < mp.month_start + INTERVAL '1 month' 
           AND (s.end_date IS NULL OR s.end_date >= mp.month_start)
      THEN TRUE 
      ELSE FALSE 
    END AS active_at_month_start,
    -- User churned during month if end_date falls within the month
    CASE 
      WHEN s.end_date >= mp.month_start 
           AND s.end_date < mp.month_start + INTERVAL '1 month'
      THEN TRUE 
      ELSE FALSE 
    END AS churned_in_month
  FROM subscriptions s
  INNER JOIN activated_users au ON s.user_id = au.user_id
  CROSS JOIN monthly_periods mp
  WHERE 
    s.start_date < mp.month_start + INTERVAL '1 month'  -- Started before month ended
    AND (s.is_trial = FALSE OR au.is_activated = TRUE)  -- Exclude unactivated trials
),

monthly_churn_metrics AS (
  SELECT 
    month_start,
    subscription_tier,
    COUNT(DISTINCT CASE WHEN active_at_month_start THEN user_id END) AS active_users_start,
    COUNT(DISTINCT CASE WHEN churned_in_month THEN user_id END) AS churned_users
  FROM subscription_status
  GROUP BY month_start, subscription_tier
)

SELECT 
  TO_CHAR(month_start, 'YYYY-MM') AS month,
  subscription_tier,
  active_users_start,
  churned_users,
  ROUND(
    CASE 
      WHEN active_users_start > 0 
      THEN (100.0 * churned_users / active_users_start) 
      ELSE 0 
    END, 
    2
  ) AS churn_rate_percent
FROM monthly_churn_metrics
WHERE active_users_start > 0  -- Only show months with active users
ORDER BY month DESC, subscription_tier;

Interview Talking Points

High-Level Approach (30 seconds)

“I’ll calculate monthly churn rate by subscription tier in four steps using CTEs. First, generate a series of monthly periods. Second, identify activated users to exclude trial-only users. Third, determine each user’s status for each month - were they active at the start, did they churn during the month. Fourth, aggregate by month and tier to calculate churn rates.”

Key Design Decisions

1. Activation filter: Used INNER JOIN with activated_users CTE to ensure we only analyze users who completed activation, explicitly excluding trial users who signed up but never activated.

2. Churn definition: Defined churn as subscription end_date falling within the month period, using date range comparisons.

3. Active at month start: A user is active if their subscription started before the month ended AND either has no end_date (still active) OR ended after the month started.

4. CROSS JOIN for time series: Used CROSS JOIN with monthly periods to create a row for each user-month combination, making it easy to track status changes over time.

5. Handling edge cases:

   • Division by zero handled with CASE statement
   • Only showing months with active users (WHERE active_users_start > 0)
   • Flexible trial exclusion using boolean logic

Verification Strategy

1. Spot check: Pick a specific month and tier, manually pull user_ids who churned, verify their end_dates fall within that month
2. Sanity check: Churn rate should be between 0-100%, active users should be >= churned users
3. Compare totals: Sum churned_users across all tiers for a month, verify against total company churn
4. Check activation filter: Query users table to confirm trial-only users (is_activated = FALSE) are excluded

Common Variations

Quarterly instead of monthly:

DATE_TRUNC('quarter', ...) instead of DATE_TRUNC('month', ...)

Include gross churn vs net churn:
Add new subscriptions (upgrades, new signups) to calculate net churn.

Cohort-based churn:
Group by signup month cohort instead of calendar month.

Revenue churn vs user churn:
Weight by subscription value instead of counting users.