You can estimate customer lifetime value in SQL by summing each customer’s observed revenue or gross-margin contribution, then grouping those totals by acquisition cohort. That produces an auditable historical measure. A churn-based formula can estimate future subscription value, but it relies on churn remaining stable and must be labeled as a projection—not an observed lifetime total.
Choose what your LTV number means
There is no single LTV calculation that answers every business question. Before writing a query, decide whether the output describes value already observed or value projected into the future, and whether it measures revenue or contribution after gross margin.
- Historical value: the revenue or contribution generated during a specified observation window. It describes what happened, not a customer’s eventual lifetime total.
- Cohort value: observed value for customers grouped by a shared starting period, such as first purchase month. It shows how value accumulates as cohorts age.
- Projected subscription LTV: an estimate of future value based on revenue per subscriber and churn. It depends on assumptions about future behavior.
- Revenue LTV: revenue attributed to customers, without subtracting delivery costs.
- Gross-margin-adjusted contribution LTV: revenue adjusted using a stated gross-margin basis. If acquisition, retention, overhead, or other costs are excluded, do not call the result full net profit.
Stripe’s overview distinguishes historical, cohort, predictive, retention-based, and RFM approaches to LTV: Stripe’s guide to calculating customer lifetime value. The SQL patterns below focus on historical aggregation, observed cohort value, and a simple subscription approximation; they do not fit a machine-learning model.
Build an auditable cohort query
The query below is a PostgreSQL-style teaching pattern. It identifies each customer’s first qualifying paid date, calculates value by elapsed month, and reports each cohort’s size and cumulative value per original customer. Replace table and column names, status rules, date logic, and value definitions to match your warehouse.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallWITH first_paid AS (
SELECT customer_id, MIN(paid_at)::date AS first_paid_date
FROM payments
WHERE status = 'paid'
GROUP BY customer_id
), customer_period_value AS (
SELECT
f.customer_id,
date_trunc('month', f.first_paid_date)::date AS cohort_month,
(date_part('year', age(date_trunc('month', p.paid_at),
date_trunc('month', f.first_paid_date))) * 12
+ date_part('month', age(date_trunc('month', p.paid_at),
date_trunc('month', f.first_paid_date))))::int AS month_number,
SUM(p.net_revenue) AS period_value
FROM first_paid f
JOIN payments p ON p.customer_id = f.customer_id
WHERE p.status = 'paid'
GROUP BY f.customer_id, cohort_month, month_number
), cohort_month AS (
SELECT cohort_month, month_number, SUM(period_value) AS cohort_value
FROM customer_period_value
GROUP BY cohort_month, month_number
), cohort_size AS (
SELECT date_trunc('month', first_paid_date)::date AS cohort_month,
COUNT(*) AS customers
FROM first_paid
GROUP BY 1
)
SELECT
m.cohort_month,
m.month_number,
s.customers,
m.cohort_value,
SUM(m.cohort_value) OVER (
PARTITION BY m.cohort_month
ORDER BY m.month_number
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) / NULLIF(s.customers, 0) AS cumulative_value_per_original_customer
FROM cohort_month m
JOIN cohort_size s USING (cohort_month)
ORDER BY m.cohort_month, m.month_number;Read the result at the right grain
Each row represents one cohort in one elapsed month. cohort_value is the cohort’s total value for that month; dividing the running cohort total by the original cohort size gives cumulative value per customer who started in that cohort. The month-zero row represents the cohort’s starting calendar month, not necessarily a complete 30-day period.
The query does not calculate active-customer retention. To report it, define “active” for the business (for example, a paid subscription at month end), count active customers at each cohort age, and divide by the original cohort size. For subscriptions, Stripe Billing defines a cohort’s start as the first time a subscriber generates positive MRR and measures retention at month end: Stripe Billing subscription analytics.
Adapt the SQL to your warehouse and accounting
This example is not production-ready as written. Date and interval functions vary across database systems. Confirm that the month-number expression behaves as intended in your database, and define how refunds, taxes, discounts, chargebacks, currency conversion, voided transactions, test data, and duplicate payments affect net_revenue. Apply the same rules to the first-paid event and subsequent value rows so cohort assignment and value measurement are consistent.
Rank #2
- Value NAS with RAID for centralized storage and backup for all your devices. Check out the LS 700 for enhanced features, cloud capabilities, macOS 26, and up to 7x faster performance than the LS 200.
- Connect the LinkStation to your router and enjoy shared network storage for your devices. The NAS is compatible with Windows and macOS*, and Buffalo's US-based support is on-hand 24/7 for installation walkthroughs. *Only for macOS 15 (Sequoia) and earlier. For macOS 26, check out our LS 700 series.
- Subscription-Free Personal Cloud – Store, back up, and manage all your videos, music, and photos and access them anytime without paying any monthly fees.
- Storage Purpose-Built for Data Security – A NAS designed to keep your data safe, the LS200 features a closed system to reduce vulnerabilities from 3rd party apps and SSL encryption for secure file transfers.
- Back Up Multiple Computers & Devices – NAS Navigator management utility and PC backup software included. NAS Navigator 2 for macOS 15 and earlier. You can set up automated backups of data on your computers.
For a margin-adjusted result, calculate contribution using an explicit gross-margin basis instead of treating revenue as profit. The appropriate treatment of costs depends on your accounting definition; the cited sources do not establish one universal convention.
Use window frames deliberately
The query’s ordered window aggregate uses an explicit running frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That makes the cumulative value at each cohort age include the current month and all earlier months in that cohort.
In PostgreSQL, an aggregate window with ORDER BY and the default frame behaves like a running aggregate. If you instead want the whole-cohort total repeated on every row, omit the window ORDER BY or specify a frame covering the full partition. Window functions preserve the rows they operate over and can appear in the SELECT list and ORDER BY clause. See the PostgreSQL 18 window-functions tutorial and PostgreSQL 18 window-function syntax documentation.
Rank #3
- Value NAS with RAID for centralized storage and backup for all your devices. Check out the LS 700 for enhanced features, cloud capabilities, macOS 26, and up to 7x faster performance than the LS 200.
- Connect the LinkStation to your router and enjoy shared network storage for your devices. The NAS is compatible with Windows and macOS*, and Buffalo's US-based support is on-hand 24/7 for installation walkthroughs. *Only for macOS 15 (Sequoia) and earlier. For macOS 26, check out our LS 700 series.
- Subscription-Free Personal Cloud – Store, back up, and manage all your videos, music, and photos and access them anytime without paying any monthly fees.
- Storage Purpose-Built for Data Security – A NAS designed to keep your data safe, the LS200 features a closed system to reduce vulnerabilities from 3rd party apps and SSL encryption for secure file transfers.
- Back Up Multiple Computers & Devices – NAS Navigator management utility and PC backup software included. NAS Navigator 2 for macOS 15 and earlier. You can set up automated backups of data on your computers.
Compare cohorts without mistaking age for performance
Cohort analysis makes differences in observed revenue and retention visible that a single portfolio average can hide. It is useful only when the cohort rule and observation window are clear.
- Use one canonical customer identifier and define the qualifying start event: a first order, first paid invoice, and first positive MRR are not interchangeable.
- Show cohort size and elapsed month alongside value. A cohort with 24 observed months has had more time to generate value than one with 3 months; do not present their partial histories as complete lifetime totals.
- Separate customer retention from revenue retention. Subscriber counts can fall while remaining customers expand, or revenue can decline because of downgrades even when customers remain. Stripe notes that subscription revenue retention reflects upgrades, downgrades, and cancellations in its Billing cohort analytics documentation.
- Expect incomplete or messy data to affect cohort interpretation. Stripe discusses data quality and reading cohort patterns in its cohort analysis guide.
Estimate subscription LTV from ARPU and churn
For a stable subscription base, a compact approximation is:
Free tools Windows power users keep installed
One-click scans. No signup required.
LTV ≈ ARPU per period × gross margin ÷ customer churn rate per same period
Rank #4
- Performance and reliability for multiple application environments
- High availability for business critical applications
- Robust SAS interface (dual port, full duplex)
- Ideal for transaction processing, database applications, analytics, high performance computing and business applications
ARPU means average revenue per subscriber for the chosen period. Use churn as a decimal and match the periods: monthly ARPU with monthly customer churn, for example. If you omit gross margin, describe the result as revenue LTV rather than contribution LTV.
This calculation extrapolates future periods; it does not sum a known customer’s actual lifetime. It is most interpretable when churn is reasonably stable. If churn changes with tenure or differs substantially by acquisition cohort, one overall rate can conceal that pattern. Zero or very small churn can also produce an extreme estimate.
Stripe Billing documents LTV as average revenue per subscriber divided by subscriber churn. Its zero-churn case assumes a 60-month lifetime to avoid division by zero; that is a Stripe product convention, not a universal rule for estimating customer lifespan. See Stripe Billing subscription analytics.
Best Value
- 3.84TB enterprise SATA solid state drive in a 2.5-inch form factor — ideal for read-intensive server and data center workloads including virtualization, content delivery, and database read replicas
- SATA 6Gb/s interface with sequential read speeds up to 555 MB/s and sequential write speeds up to 530 MB/s for consistent, high-throughput data access
- 3D TLC NAND flash with 1 Drive Write Per Day (DWPD) endurance rating and 7,008 TBW total write endurance over a standard 5-year period
- 96,000 random read IOPS and 35,000 random write IOPS with enterprise-grade power loss protection and error correcting code for data integrity in mission-critical environments
- Dual Dell/SK Hynix label (Dell DPN 03GDK0) — fully compatible with any system supporting a standard SATA interface, not limited to Dell systems; 2,000,000-hour MTBF reliability rating
Validate the inputs before using the result
SQL makes the arithmetic inspectable, but it cannot resolve inconsistent definitions or faulty joins on its own. Before using an LTV result in a decision, run these checks:
- Reconcile SQL revenue totals with finance or billing-source totals for a fixed period, using the same currency and revenue rules.
- Check that joins do not multiply customers or transactions; inspect the row counts and keys at each query grain.
- Manually inspect a few customer timelines, including refunds, multiple purchases, subscription changes, and cancellations where relevant.
- Confirm whether the customer key represents an individual, account, household, or another unit, and use it consistently.
- Keep revenue, contribution, customer churn, and revenue churn as distinct measures in tables and dashboards.
Choose the method that fits the decision
| Method | What it measures | Main assumption or limit | Best use |
|---|---|---|---|
| Historical customer aggregation | Value already generated in a defined observation window | Does not by itself predict future value or a complete lifetime | Auditable summaries of realized revenue or contribution |
| Cohort-period aggregation | Observed value and, if added, retention by acquisition cohort and elapsed period | Cohorts have unequal observation time; definitions and data maturity matter | Comparing trajectories and identifying differences hidden by an overall average |
| ARPU divided by churn | Approximate future subscription value | Assumes churn is stable; small rates can inflate the result | A compact cross-check when subscription revenue and churn periods align |
For an inspectable SQL baseline, start with historical totals and cohort trajectories. Use the churn formula as a separate, explicitly projected estimate rather than blending it into observed value.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
