Coding questions · Intuit
Compute churn and revenue churn in SQL
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:
- 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).
Compute August-2025 churn and revenue metrics from monthly snapshots
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.
Table subscription_monthly_snapshot
| column | type | constraints |
|---|---|---|
| user_id | VARCHAR(10) | NOT NULL |
| snapshot_date | DATE | NOT NULL |
| is_active | SMALLINT | NOT NULL |
| mrr | DECIMAL(10,2) | NOT NULL |
| load_ts | TIMESTAMP | NOT NULL |
Sample data:
| is_active | load_ts | mrr | snapshot_date | user_id |
|---|---|---|---|---|
| 1 | 2025-08-01 00:00:00 | 20 | 2025-07-31 | u1 |
| 0 | 2025-09-01 00:00:00 | 0 | 2025-08-31 | u1 |
| 1 | 2025-08-01 00:00:00 | 50 | 2025-07-31 | u2 |
| 1 | 2025-09-01 00:00:00 | 50 | 2025-08-31 | u2 |
| 1 | 2025-08-01 00:00:00 | 30 | 2025-07-31 | u3 |
| 1 | 2025-09-01 00:00:00 | 20 | 2025-08-31 | u3 |
| 0 | 2025-08-01 00:00:00 | 0 | 2025-07-31 | u4 |
| 1 | 2025-09-01 00:00:00 | 15 | 2025-08-31 | u4 |
| 1 | 2025-08-01 00:00:00 | 15 | 2025-07-31 | u5 |
| 0 | 2025-09-01 00:00:00 | 0 | 2025-08-31 | u5 |
| 1 | 2025-08-01 00:00:00 | 25 | 2025-07-31 | u6 |
| 1 | 2025-09-01 00:00:00 | 35 | 2025-08-31 | u6 |
| 1 | 2025-08-01 00:00:00 | 40 | 2025-07-31 | u7 |
| 0 | 2025-09-01 00:00:00 | 0 | 2025-08-31 | u7 |
Expected output
The query should return a single row with the August-2025 churn metrics for the July-2025 active cohort. For the provided sample data, there are 6 active customers in July, 3 of whom fully churn by August. July cohort MRR is 180. MRR lost to logo churn is 75, contraction MRR is 10, and expansion MRR is 10, yielding the metrics below.
| gross_revenue_churn | logo_churn_count | logo_churn_rate | net_revenue_retention |
|---|---|---|---|
| 0.4722 | 3 | 0.5 | 0.5833 |
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.