Calculate a cohort metric with a fixed denominator

A cohort groups entities by a shared starting period or event. A retention metric compares later behavior with a defined baseline. Before writing SQL, specify cohort membership, currency, period boundaries, treatment of refunds and whether expansion can raise retention above one hundred percent.

A fixed denominator prevents a misleading result. If customers who leave disappear from the denominator, a report can show perfect retention among survivors while ignoring churn. Preserve the original cohort and left-join later activity so zero-revenue members remain represented.

Gross and net revenue retention use different expansion treatment. In this teaching exercise, capped retained revenue limits each customer's later revenue to its baseline before summing. That is a defined metric for the exercise, not a universal naming rule. State the formula so the reader can reproduce it.

Historical corrections complicate the report. A refund can change the baseline or later value, depending on the accounting contract. Decide whether prior published cohorts are restated and how versions are exposed. Do not quietly recompute history under a new definition while presenting the chart as unchanged.

Build a small hand-calculated example before a large query. Include one expansion, one contraction and one churned customer. This exposes incorrect joins and denominator filtering. A good interview answer also checks grain: multiple invoices per customer-month must be aggregated before applying a customer-level cap. Applying the cap to each invoice separately changes the result.

Keep all money in a defined common unit or report currencies separately. Adding unlike currencies can produce a precise-looking number with no valid business interpretation.

For this workbook, define baseline revenue B_i for each customer in the fixed cohort and later-period revenue L_i in the same currency. Net retained revenue is sum(L_i) divided by sum(B_i). The capped measure is sum(min(L_i, B_i)) divided by sum(B_i), under the exercise's nonnegative-revenue assumption. The formula must change or be explicitly extended if refunds produce negative amounts or baseline values are zero.

1-- Teaching sketch: baseline has one row per cohort customer.2WITH later AS (3  SELECT customer_id, SUM(amount) AS later_amount4  FROM invoices5  WHERE period = '2026-02'6  GROUP BY customer_id7)8SELECT9  SUM(LEAST(COALESCE(l.later_amount, 0), b.baseline_amount))10    / NULLIF(SUM(b.baseline_amount) * 1.0, 0) AS capped_retention11FROM baseline AS b12LEFT JOIN later AS l13  ON l.customer_id = b.customer_id;

The query assumes the baseline table already contains the correct cohort, one common currency, and one row per customer. It uses a floating-point or decimal-compatible division expression to avoid accidental integer division, but a real financial implementation should choose explicit decimal types and rounding rules. NULLIF prevents division by zero; the resulting null must be explained as an undefined metric, not converted silently into perfect or zero retention.

The table yields net retention of 60 percent and capped retention of 50 percent. Capping each invoice separately would retain 70 +70 for A, incorrectly allowing 140 against a 100 baseline. The aggregation grain must match the cap grain. A query can use familiar functions and still answer the wrong metric if it applies them in the wrong order.

The fixed cohort is another data object, not a filter reconstructed from later activity. Preserve its membership and baseline values with a version or reproducible input definition. A left join from that cohort keeps churned customers. An inner join can remove them from both numerator and denominator, making remaining customers appear healthier while hiding churn. Check customer counts before and after the join.

Corrections require a publication policy. Suppose a backdated refund reduces customer B's baseline from 200 to 180. If history is restated, the denominator changes from 400 to 380 and the capped numerator may or may not change depending on later revenue. In the table, B's later 100 remains below both baselines, so the capped numerator stays 200 and the revised percentage becomes about 52.63 percent. That change does not mean retention improved operationally; it reflects corrected baseline data.

An alternative accounting policy may book the refund in a later period rather than restate the baseline. Neither choice should be invented by the pipeline author. Ask for the metric definition, retain the policy revision and expose restated periods. An interview answer should calculate the consequence of the stated policy and separate it from a business claim about customer behavior.

The public Tracksuit sample supports the relevance of a subscription/cohort modeling task, but these figures, formulas and SQL are original practice. Do not claim that this exact query or answer was asked by that employer. The lesson's value is the completed reasoning from grain to denominator to correction, which transfers to many analytical exercises.

Test zero later revenue, expansion, contraction, duplicate invoices, zero baseline and multiple currencies. Some cases should produce a result; others should fail validation or remain undefined under the chosen contract. A good answer names those boundaries rather than forcing every input into one percentage.

Teaching January cohort: customer A baseline 100, B 200, C 100, all in the same currency. In February they produce 150, 100 and zero. Baseline is 400. Net retained revenue is 250/400 = 62.5%. Under the stated per-customer cap, retained values are min(150,100)=100, min(100,200)=100 and zero, so capped retention is 200/400 = 50%. If churned C is removed from the denominator, the result becomes 200/300 = 66.7%, which answers a different and misleading question.

A cohort has baseline amounts 80, 120 and 200. Later amounts are 100, 60 and zero. Compute net and per-customer-capped retention using a fixed denominator.

Baseline is 400. Net retention is 160/400 = 40%. Capped retained value is 80 + 60 + 0 = 140, so capped retention is 35%. Keep the zero-revenue customer in the cohort. Verify one row per customer-period before the cap. If two later invoices total 100 for the first customer, sum them before comparing against the baseline eighty.

Score out of four: one point for the correct result, one for showing the intermediate reasoning, one for identifying the stated failure case, and one for a verification that could disprove the answer. Do not award the reasoning point for a tool name alone.

“Survivors define the cohort denominator.” Cohort membership is fixed at baseline. Removing churned members changes the question.

“A higher restated percentage proves customer behavior improved.” A corrected denominator can change the percentage without any new later-period activity. Explain the data revision.

Evidence class: recommended. Original practice.

Why can a retention query look correct but overstate performance?

Strong answer: An inner join can remove churned customers from the baseline, or the query can apply caps at the wrong grain. I would fix cohort membership first, aggregate customer-period revenue, left-join later periods and compare against a hand-calculated case.

Follow-up: How should a backdated refund change a previously published cohort?

Weak answer indicators: Changing the denominator each month; adding currencies without a rule; applying a per-customer cap at invoice grain.

Technical references: dbt data tests; Tracksuit public Senior Data Engineer task. Sources support the documented mechanisms. The numbers, decisions, rubrics and interview prompts in this lesson are original teaching examples, not measurements or employer question claims.