Compute churn and revenue churn in SQL
Company: Intuit
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
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).
2) State the numeric outputs your SQL would return for this exact sample.
3) Explain how your logic changes if you measure churn mid-month on transactional cancels instead of snapshots (mention grace periods and partial-month proration).
Overview: 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.
Read the full Intuit Data Scientist interview experience this question came from
You receive end-of-month subscription snapshots in a PostgreSQL table subscription_monthly_snapshot(user_id, snapshot_date, is_active, mrr, load_ts). Using this table and the sample data below, write a PostgreSQL query that outputs, for August 2025, the following metrics in a single row: logo_churn_count, logo_churn_rate, gross_revenue_churn, and net_revenue_retention. Use exactly these definitions: (1) A customer is active in a month if is_active = 1 on that month's snapshot_date. (2) Logo churn in August 2025 = customers active on 2025-07-31 but not active on 2025-08-31. (3) Logo churn rate = logo_churn_count divided by the active customer count on 2025-07-31. (4) Gross revenue churn = MRR lost from logo churn plus contraction MRR among customers active on 2025-07-31, divided by total July 2025 MRR. (5) Net revenue retention = July 2025 MRR minus churn loss and contraction plus expansion, divided by July 2025 MRR; expansion and contraction consider only customers active on 2025-07-31, and reactivations or new logos are excluded. Your query must deduplicate multiple snapshots per (user_id, snapshot_date) by keeping the latest load_ts, treat missing month rows as is_active = 0 and mrr = 0 with full outer join logic, and treat NULL mrr as 0. Return the numeric values shown by the sample data.
Tables
subscription_monthly_snapshot(user_id VARCHAR(10), snapshot_date DATE, is_active SMALLINT, mrr DECIMAL(10,2), load_ts TIMESTAMP)
Hints
- First deduplicate snapshots per (user_id, snapshot_date) using ROW_NUMBER ordered by load_ts DESC, then pick rn = 1.
- Full outer join July and August snapshots on user_id, COALESCE missing sides to is_active = 0 and mrr = 0, and aggregate churn, contraction, and expansion only over the July-2025 active cohort.