This is where I spent most of my time and still felt shaky at the end.
Start by deduplicating the snapshots to one row per user per month using the latest load timestamp, then perform a full outer join between July and August 2025 snapshots to capture churned, retained, and new users. Compute logo churn count and rate from the join, and calculate gross revenue churn and net revenue retention using MRR with nulls treated as zero.
Pro tip: Explicitly state your assumptions about the definition of churn (e.g., a user with MRR > 0 in July and either missing or MRR = 0 in August) and how you handle edge cases like reactivations or upgrades, as this demonstrates business acumen and prevents ambiguity.
Use a window function like ROW_NUMBER() partitioned by user_id and snapshot_date, ordered by load_timestamp DESC, to keep only the most recent record per user per month.
Join the deduplicated July and August snapshots on user_id using FULL OUTER JOIN to include users present in either month, ensuring churned and new users are captured.
Identify churned logos as users with MRR > 0 in July and (missing in August or MRR = 0 in August). Compute logo churn count and rate (churned / July active logos).
Calculate gross revenue churn as sum of July MRR for churned users, and net revenue retention as (sum of August MRR from retained/expanded users) / (sum of July MRR from all July users), treating null MRR as zero.
Use COALESCE to replace null MRR with 0 in all calculations, and ensure the final output includes the four metrics for August 2025.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Worked through this on paper after writing the query.
First, clearly define each metric and the exact calculation logic (e.g., logo churn count = number of customers who canceled in the period; logo churn rate = churned customers / starting customers; gross revenue churn = lost MRR from churned customers; net revenue retention = (starting MRR + expansion - contraction - churn) / starting MRR). Then walk through the sample data row by row, applying the formulas to compute the numeric outputs, and state the final numbers explicitly.
Pro tip: Always state your assumptions about the time period and whether you're using beginning-of-period or average customer count for the denominator, as these choices can significantly change the churn rate and NRR. Also, double-check that you're not double-counting revenue from customers who both expanded and churned.
Write down the exact formula for each metric: logo churn count, logo churn rate, gross revenue churn, and net revenue retention. Clarify whether churn is measured over a month, quarter, or year, and whether the denominator uses starting customers or average customers.
From the sample data, extract the starting customer count, number of churned customers, starting MRR, expansion MRR, contraction MRR, and churned MRR. Ensure you understand which customers are considered 'churned' (e.g., fully canceled vs. downgraded).
Calculate logo churn count as the number of customers who canceled. Compute logo churn rate as churned customers divided by starting customers (or average, if specified). Calculate gross revenue churn as the sum of MRR lost from churned customers. Compute net revenue retention as (starting MRR + expansion - contraction - churn) / starting MRR, expressed as a percentage.
Double-check your arithmetic and ensure the units are consistent (e.g., MRR in dollars, rates as percentages). Present the four numbers clearly, labeling each metric and rounding appropriately (e.g., two decimal places for rates).
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Honestly the part I felt least prepared for.
Start by contrasting snapshot-based churn (monthly active/inactive flags) with event-based churn (transactional cancellation events), highlighting the shift from state-based to event-stream logic. Then walk through the key considerations: defining the churn event, handling grace periods, proration, and aligning with business definitions. Finally, discuss how to validate and operationalize the new logic.
Pro tip: Emphasize that event-based churn requires a clear event taxonomy and a well-defined observation window; without them, you risk double-counting or missing churn. Also, proactively mention that you'd align with Finance and Product to ensure the churn definition matches revenue recognition and customer success triggers.
Clarify what constitutes a cancellation event (e.g., user-initiated, system-triggered) and set a consistent observation window (e.g., 30 days) to avoid ambiguity. This replaces the monthly snapshot's implicit period definition.
Determine whether churn is recorded at cancellation request or at the end of the grace period. Decide if grace periods should be treated as active or churned, and ensure consistency across segments.
Decide how to attribute churn for partial months: count the customer as churned in the month of cancellation or the following month? Consider proration for revenue metrics and how it affects MRR/ARR calculations.
Shift from counting active/inactive customers at month-end to aggregating events over time. Use event timestamps to compute churn rates, cohort retention, and survival analysis.
Compare event-based churn with snapshot-based churn to identify discrepancies. Socialize the new logic with Finance, Product, and Customer Success to ensure alignment with business definitions and reporting needs.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.