Compute churn and revenue churn in SQL

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during HR Screen rounds at Intuit.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Intuit during technical interviews.

Question

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).

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview