Articles
SQL Customer Lifetime Value Interview Questions: Cohorts, Refunds, and Observation Windows
Practice SQL customer lifetime value interview questions with cohort denominators, partial refunds, 90-day windows, tested queries, and clear LTV limits.
SQL customer lifetime value interview questions are usually won before the final SUM. Define the customer population, acquisition cohort, observation window, and refund policy first. Then calculate an observed value per customer. A finite transaction history does not, by itself, estimate the customer's entire future lifetime.
For data analyst and data scientist interviews, a defensible starting point is net revenue from each customer's first 90 days of orders, restated for successful refunds known at a stated cutoff. That is a specific historical metric, not contribution profit or predictive LTV.
The example below is original interview practice. Provider and SQL semantics are linked to official documentation checked September 21, 2026. The fixture is synthetic; it does not represent a company's customers or interview questions.
What should you clarify before calculating customer lifetime value?
Ask what decision the number will support. Comparing acquisition channels requires a consistent acquisition definition and denominator. Forecasting future contribution requires a model, costs, and a horizon. A customer leaderboard may instead need observed spend to date. These outputs answer different questions even when somebody calls all three “LTV.”
Stripe's subscription analytics, for example, defines subscriber LTV using average revenue per user divided by subscriber churn rate and describes the result as an estimate. That definition is not interchangeable with a 90-day cohort calculation. Cite the definition you actually implement rather than borrowing a familiar label. Stripe subscription analytics
For this exercise, agree on six choices: signup is acquisition; the unit is one customer; the purchase window is 90 elapsed days; money is USD cents; only completed orders and successful refunds count; and the cutoff is May 1, 2026 at 00:00 UTC, exclusive. Taxes, disputes, fees, and service costs are outside the fixture.
The output will be one row per signup month, with eligible customers, total net cents, and average net dollars. Call it observed_90d_net_order_revenue, not predicted_lifetime_value. A precise name prevents downstream users from quietly turning a historical summary into a forecast.
How do you keep cohorts comparable?
A cohort groups customers by a shared starting event. Here, signup month defines membership, while each customer's actual signup timestamp defines their individual 90-day window. A January 31 signup should not lose 30 observation days merely because another customer joined January 1.
Include a customer only when signup_at + INTERVAL '90 days' <= as_of. This maturity rule prevents a recent cohort's shorter history from masquerading as lower value. Do not divide an immature customer's revenue by days observed and multiply by 90 without labeling that operation as an extrapolation with additional assumptions.
Use a half-open purchase interval: order_at >= signup_at and order_at < signup_at + INTERVAL '90 days'. An order exactly at the upper boundary belongs outside this metric. Ninety days is also not the same as three calendar months; establish which the prompt requires.
Keep non-purchasers. If acquisition means signup, somebody who signs up and spends nothing still costs a place in the denominator. Starting from orders silently changes the question to value per purchasing customer. Reactivation does not create a new acquisition cohort here, and account merges need an explicit identity policy before applying this logic to real data.
Worked example: three mature customers and one tempting exclusion
Set the snapshot cutoff to May 1. Customers A, B, and C signed up January 1. Customer D signed up February 15 and is not yet 90 days old. All amounts below are dollars for readability; the query stores cents.
| Customer | Included orders | Successful refunds | Net value |
|---|---|---|---|
| A | $150 | $30 | $120 |
| B | $0 | $0 | $0 |
| C | $100 | $100 | $0 |
| D | $80 | $0 | Excluded |
A has two completed orders: $100 on January 2 and $50 on February 1. The first receives a $20 refund on January 15 and another $10 refund on April 15. C's $100 order is fully refunded in February. B never orders. D has an $80 order, but that customer's purchase window ends May 16.
The mature January cohort has $250 gross revenue minus $130 successful refunds, leaving $120 across three customers: $40 per acquired customer. Dropping B produces $60, a different metric. Including D as though fully observed produces $50 across four customers, which mixes observation ages rather than measuring improvement.
There are two clocks. A's April 15 refund happens after the 90-day purchase window, yet before the reporting cutoff. We attribute it back to an included order. If instead we counted only refunds occurring inside each customer's first 90 days, the January numerator would be $130 and the average about $43.33. Neither policy should be left implicit.
How do refunds change the observation window?
An order-cohort view asks what ultimately remained from orders placed during the purchase window, as currently known. A cash-timing view asks which collections and refunds happened during the window. Our April refund belongs in the first view but falls outside the second view's January-to-April cash window.
Do not describe the example as final lifetime profitability. Additional refunds can arrive after May 1 and restate it again. If comparing cohorts, also address unequal refund maturation: an older cohort has had longer for reversals to appear. Options include a common post-window refund lag, several dated snapshots, or a clearly labeled latest-known view. The right choice depends on the business question.
Stripe permits partial refunds and multiple refunds against one payment, subject to the remaining refundable amount. Refunds also have lifecycle states. A requested refund is not automatically a successful refund; the fixture's success-only policy must be enforced before aggregation. Stripe refunds documentation
Our analytical tables contain status and success time as known at the snapshot. A mutable current-status table cannot reproduce historical knowledge reliably. For historical backtests, preserve refund status history or snapshot versions and distinguish when the event occurred from when the warehouse learned about it.
SQL solution: aggregate refunds before joining customers
Assume customers(customer_id, signup_at), orders(order_id, customer_id, order_at, amount_cents, status), and refunds(refund_id, order_id, succeeded_at, amount_cents, status). IDs are unique within their tables, refund references are valid, and timestamp columns represent UTC. The fixture contains only USD. These constraints are part of the answer, not facts to assume about an unfamiliar warehouse.
The following PostgreSQL-style SQL is executed locally against the synthetic fixture in DuckDB. This verifies the example's arithmetic and join behavior, not a PostgreSQL execution plan or production billing integration.
WITH params AS (
SELECT TIMESTAMP '2026-05-01 00:00:00' AS as_of
), eligible AS (
SELECT c.customer_id, c.signup_at,
DATE_TRUNC('month', c.signup_at) AS cohort_month
FROM customers c CROSS JOIN params p
WHERE c.signup_at + INTERVAL '90 days' <= p.as_of
), refunds_by_order AS (
SELECT r.order_id, SUM(r.amount_cents) AS refund_cents
FROM refunds r CROSS JOIN params p
WHERE r.status = 'succeeded'
AND r.succeeded_at < p.as_of
GROUP BY r.order_id
), orders_in_window AS (
SELECT e.customer_id, o.order_id,
o.amount_cents - COALESCE(r.refund_cents, 0)
AS net_cents
FROM eligible e
JOIN orders o ON o.customer_id = e.customer_id
AND o.status = 'completed'
AND o.order_at >= e.signup_at
AND o.order_at < e.signup_at + INTERVAL '90 days'
LEFT JOIN refunds_by_order r ON r.order_id = o.order_id
), customer_value AS (
SELECT e.customer_id, e.cohort_month,
COALESCE(SUM(o.net_cents), 0) AS net_cents
FROM eligible e
LEFT JOIN orders_in_window o
ON o.customer_id = e.customer_id
GROUP BY e.customer_id, e.cohort_month
)
SELECT cohort_month, COUNT(*) AS eligible_customers,
SUM(net_cents) AS cohort_net_cents,
ROUND(SUM(net_cents) / (100.0 * COUNT(*)), 2)
AS observed_90d_net_order_revenue_usd
FROM customer_value
GROUP BY cohort_month
ORDER BY cohort_month;
The January output is three customers, 12,000 net cents, and 40.00 dollars per customer. Refund aggregation creates one row per order before joining. The customer aggregation then creates exactly one row per eligible customer before calculating the cohort average.
Joining raw refund rows directly to orders would repeat A's $100 order once per refund. SUM(DISTINCT amount_cents) is not a repair: two legitimate orders can have identical amounts. Preserve identifiers and grain instead. For broader payment-versus-order diagnostics, use the separate SQL revenue reconciliation walkthrough.
The final left join retains B. PostgreSQL documents that SUM over no selected rows returns null, so the explicit COALESCE assigns zero under our no-purchase convention. That is different from filling missing ingestion data with zero. PostgreSQL aggregate functions
Which boundary tests should you explain aloud?
Start with the fixture's known answer, then change one condition at a time. Add an order exactly on April 1 for a January 1 signup: it must not change the result. Add one immediately before the boundary: it must. Add a failed refund or a successful refund exactly at the May 1 cutoff: neither enters this snapshot.
Test a second partial refund against one order to expose fan-out. Test an eligible customer with no orders to expose denominator loss. Test a customer whose window ends exactly at the cutoff: that customer is mature, even though a refund timestamp exactly at the cutoff remains excluded. These inequalities serve different purposes.
Also test invalid references, duplicate refund IDs, and refunds exceeding original amounts. Treat those as data-quality exceptions, not permission to clamp every negative value to zero. A negative result can reveal a scope mismatch or broken input. With real chargebacks, fees, or credits, negative contribution can also be meaningful under a different contract.
Explain where predicates live. Filtering a nullable order column in the final WHERE after a left join can discard non-purchasers. PostgreSQL's join documentation distinguishes join restrictions from restrictions applied after the join; that difference is exactly what this denominator needs. PostgreSQL table expressions
When does observed revenue become contribution or predicted LTV?
Subtracting refunds does not subtract all costs. Contribution requires agreed variable costs: fulfillment, payment processing, service delivery, or other relevant items. Allocate each cost at a compatible grain before joining it. Keep acquisition cost separate unless the requested metric explicitly includes it, and never combine currencies without a stated conversion convention.
Prediction requires further assumptions about retention, future purchases, margins, and horizon. A simple ARPU/churn ratio assumes enough stability to make that shortcut useful. Zero observed churn does not establish infinite customer value. A finite-horizon forecast should state what data was available at prediction time and evaluate later outcomes without leaking future refunds or behavior into features.
For an interview, a strong transition is: “I can validate observed 90-day net order revenue first. To estimate future contribution, I would add a specified cost model and retention or purchase model, then evaluate forecasts on later cohorts.” That separates a reproducible SQL result from the modeling work still required.
Practice the five decisions, not just the final query
| Practice question | Practice focus |
|---|---|
| Calculate Cohort Retention | Cohort base |
| Write SQL revenue and anomaly queries | Refund links |
| Write Transaction Analytics SQL Queries | Gross versus net |
| Write rolling-window SQL over weekly cohorts | Window rules |
| Transform messy transactions with pandas | Snapshot hygiene |
Before opening a solution, write the metric name, unit, denominator, time boundaries, and expected result on four customers. Then explain which rows survive each stage.