Katkat
Back

Compute churn and revenue churn in SQL

IntuitData Scientist · HR Screen
MediumData ScientistHR ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates a candidate's ability to compute subscription churn and revenue retention metrics from monthly snapshots, exercising SQL data-manipulation competencies such as handling missing or null MRR values, deduplicating snapshot loads, joining across months, and aggregating MRR changes.

You receive monthly end-of-month subscription snapshots and must compute August-2025 churn metrics. Schema and sample data:

Table: subscription_monthly_snapshot(user_id STRING, snapshot_date DATE, is_active TINYINT, mrr DECIMAL(10,2))

+---------+---------------+-----------+-------+ | user_id | snapshot_date | is_active | mrr | +---------+---------------+-----------+-------+ | u1 | 2025-07-31 | 1 | 20.00 | | u1 | 2025-08-31 | 0 | 0.00 | | u2 | 2025-07-31 | 1 | 50.00 | | u2 | 2025-08-31 | 1 | 50.00 | | u3 | 2025-07-31 | 1 | 30.00 | | u3 | 2025-08-31 | 1 | 20.00 | | u4 | 2025-07-31 | 0 | 0.00 | | u4 | 2025-08-31 | 1 | 15.00 | | u5 | 2025-07-31 | 1 | 15.00 | | u5 | 2025-08-31 | 0 | 0.00 | | u6 | 2025-07-31 | 1 | 25.00 | | u6 | 2025-08-31 | 1 | 35.00 | | u7 | 2025-07-31 | 1 | 40.00 | | u7 | 2025-08-31 | 0 | 0.00 | +---------+---------------+-----------+-------+

Definitions (use exactly these):

  • Active on a month = is_active = 1 on that month’s snapshot_date.
  • Logo churn in August-2025 = customers active on 2025-07-31 but not active on 2025-08-31.
  • Logo churn rate (Aug-2025) = logo_churn_count / active_count_on_2025-07-31.
  • Gross revenue churn (Aug-2025) = (MRR lost from logo churns + MRR contractions among customers active on 2025-07-31) / total July-2025 MRR.
  • Net revenue retention (Aug-2025 cohort) = (July-2025 MRR − churn_loss − contraction + expansion) / July-2025 MRR, where expansion/contraction consider only customers active on 2025-07-31; exclude reactivations/new logos (e.g., u4).

Tasks:

  1. Write ANSI-SQL that outputs for Aug-2025: logo_churn_count, logo_churn_rate, gross_revenue_churn, net_revenue_retention. Your query must:
  • Correctly handle customers missing from one of the months (treat missing as is_active = 0, mrr = 0 via full outer join logic).
  • Deduplicate if multiple snapshots per (user_id, snapshot_date) exist by keeping the latest load (assume a hidden column load_ts exists; if not present, state how you’d resolve deterministically).
  • Be robust to null mrr values (treat null as 0).
  1. State the numeric outputs your SQL would return for this exact sample.
  2. Explain how your logic changes if you measure churn mid-month on transactional cancels instead of snapshots (mention grace periods and partial-month proration).