Recap — LTV Three Ways | SQL Interview & Case Lab

"What's the LTV of our customers?" is the most-asked, worst-understood question in SaaS analytics. There isn't one LTV. There are at least three, each with a different SQL pattern.

1) Historic LTV — what each customer has paid so far

The simplest version. For every customer, sum every dollar they have ever paid.

SELECT
    customer_id,
    SUM(amount) AS historic_ltv
FROM payments
GROUP BY customer_id;

This is honest about the past but says nothing about the future. Useful for who are our most valuable customers, useless for what's the LTV of a customer we acquired yesterday.

2) Cohort LTV — average revenue from each acquisition cohort over time

Group customers by the month (or week) they signed up, then watch how cumulative revenue evolves.

WITH cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', first_payment_date) AS cohort_month
    FROM customer_first_payment
),
revenue_by_cohort AS (
    SELECT
        c.cohort_month,
        DATE_TRUNC('month', p.payment_date) AS revenue_month,
        SUM(p.amount) AS revenue,
        COUNT(DISTINCT c.customer_id) AS cohort_size
    FROM cohorts c
    JOIN payments p ON p.customer_id = c.customer_id
    GROUP BY 1, 2
)
SELECT
    cohort_month,
    revenue_month,
    SUM(revenue) OVER (PARTITION BY cohort_month ORDER BY revenue_month) AS cumulative_revenue,
    cohort_size,
    SUM(revenue) OVER (PARTITION BY cohort_month ORDER BY revenue_month)::float / cohort_size AS cumulative_ltv
FROM revenue_by_cohort;

The output: for the Jan cohort, after 3 months avg LTV is X; after 6 months, Y. That's the real LTV curve.

3) Predictive LTV — the naive analyst version

The classic interview answer: ARPU / churn_rate.

WITH metrics AS (
    SELECT
        AVG(monthly_revenue) AS arpu,
        AVG(CASE WHEN churned_this_month THEN 1.0 ELSE 0 END) AS monthly_churn
    FROM customer_month
    WHERE month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
)
SELECT arpu / monthly_churn AS predicted_ltv FROM metrics;

It's a back-of-envelope. It assumes constant churn forever (wrong) and constant ARPU per customer (also wrong). In an interview you should compute it, then immediately call out its limitations. That's the signal of a senior analyst.

Which one when?

Question	LTV flavour
Who are our top customers?	Historic LTV
What's the payback period on Q1 acquisitions?	Cohort LTV
Quick estimate for a board deck?	Predictive (ARPU / churn)
Comparing acquisition channels?	Cohort LTV by channel

Common interview traps

Using historic LTV for new customers — gives misleadingly low values for recent signups who haven't had time to generate revenue yet.
Forgetting to discount future cash flows — a strict definition requires NPV, but most interviews don't go there unless you mention it first.
Mixing gross vs net — LTV usually means gross revenue. If asked for contribution margin LTV, subtract COGS first.

Takeaway

When asked "compute LTV", the right answer is "which one?" Show you know there are several, pick the one that fits the use case, and write the SQL. That's the difference between an analyst who has seen the question and one who has answered it before.