Compute churn and revenue churn in SQL
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:
- 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).
- State the numeric outputs your SQL would return for this exact sample.
- Explain how your logic changes if you measure churn mid-month on transactional cancels instead of snapshots (mention grace periods and partial-month proration).