Annual vs monthly subscription mix in SQL
Contents:
Why billing-interval mix matters
Picture the scenario: it is Sunday night, the CFO at a Series B SaaS company drops a Slack message asking for "annual vs monthly split for the board deck by Monday morning." Behind that nine-word ask is a strategic decision. Annual contracts deliver upfront cash, lock customers in for twelve months, and post churn rates a fraction of what month-to-month plans see. Monthly plans look better on the gross MRR line because nothing has been discounted, but they walk out the door at three or four times the rate. The mix between the two billing intervals is a directional bet on whether the company wants to maximize cash collection today or top-line MRR optics this quarter.
Most growth-stage SaaS companies — Notion, Linear, Vercel — pitch annual at a 15 to 20 percent discount precisely to swing this lever. The discount looks like burned revenue at the SKU level, but it locks in cash, lowers voluntary churn, and shifts the customer base toward a healthier long-term shape. A rising annual share means the discount is converting; a falling share usually means pricing got out of step or the sales motion regressed.
This post walks through the SQL to compute the annual versus monthly mix end to end: base breakdown, ARPU and churn by interval, discount economics over three years, mix dynamics, pitfalls finance teams catch first, and an FAQ from real SaaS analyst interviews. Every query is Postgres style and ports to Snowflake, BigQuery, or Databricks with minor edits.
The base mix breakdown
The first job is the headline split — what share of active subscriptions sits on annual versus monthly, and what share of MRR each interval contributes. The query below uses two windowed SUM calls to compute the percent of subs and percent of MRR in a single pass.
SELECT
billing_interval,
COUNT(*) AS active_subs,
SUM(mrr) AS total_mrr,
COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER () AS pct_of_subs,
SUM(mrr) * 100.0 / SUM(SUM(mrr)) OVER () AS pct_of_mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY billing_interval;Two columns do the work. pct_of_subs tells you the customer-count share — the metric that lives in adoption decks. pct_of_mrr tells you the revenue share, which is the metric finance decks use. The two numbers almost never match, and the gap is where the story is. If annual is 30 percent of subscriptions but 50 percent of MRR, your annual customers are materially more valuable per head, and the next move is to nudge monthly customers toward annual.
A second pattern shows up reliably across SaaS portfolios: when annual share crosses 50 percent of MRR, churn and forecasting both become noticeably easier to manage. Locked-in revenue compounds. That is why Series B and later companies often run explicit campaigns to push annual share above that threshold, even at a higher discount.
ARPU and churn by interval
The second query splits the two metrics that drive almost every annual-versus-monthly conversation: average MRR per subscription and churn rate, both broken out by billing interval. The trick is to compute tenure honestly so that monthly subs are not unfairly penalized for having only a few months in the window.
SELECT
billing_interval,
AVG(mrr) AS avg_mrr,
SUM(CASE WHEN churned_at IS NOT NULL THEN 1 ELSE 0 END)::NUMERIC
* 100 / COUNT(*) AS churn_pct,
AVG(EXTRACT(EPOCH FROM (COALESCE(churned_at, CURRENT_DATE)
- subscription_start)) / 86400) AS avg_tenure_days
FROM subscriptions
WHERE subscription_start >= '2025-01-01'
GROUP BY billing_interval;Numbers to expect in a healthy mid-market SaaS: monthly subs churn 30 to 50 percent over twelve months, while annual subs land at 5 to 15 percent over the same window. The gap is the entire reason the discount exists. If your annual churn is north of 20 percent, something is wrong with onboarding, success, or pricing tiers, and the discount is buying you very little.
A modelling choice hides in churn_pct: it is a cumulative churn share over the cohort window, not an annualized rate. Annual churn measured per year is already apples-to-apples; for monthly churn, the right annualization is 1 - (1 - monthly_rate)^12, not monthly_rate × 12. The interview version of this distinction shows up in the SQL window functions deep dive.
The annual discount economics
The hardest question in this whole topic is whether the annual discount actually pays for itself. The short answer is almost always yes, because lower churn compounds over multiple years. The long answer requires projecting expected revenue per user across years one, two, and three, holding churn rate as a per-period decay.
WITH revenue_by_type AS (
SELECT
billing_interval,
AVG(mrr * 12) AS annual_revenue_per_user,
AVG(discount_pct) AS avg_discount,
SUM(CASE WHEN churned_at IS NOT NULL THEN 1 ELSE 0 END)::NUMERIC
/ COUNT(*) AS churn_rate
FROM subscriptions
WHERE subscription_start >= '2025-01-01'
GROUP BY billing_interval
)
SELECT
billing_interval,
annual_revenue_per_user,
annual_revenue_per_user * (1 - churn_rate) AS expected_year_1_revenue,
annual_revenue_per_user * POWER(1 - churn_rate, 2) AS expected_year_2_revenue,
annual_revenue_per_user * POWER(1 - churn_rate, 3) AS expected_year_3_revenue
FROM revenue_by_type;The pattern that emerges in almost every SaaS warehouse: by year two, cumulative expected revenue from an annual customer exceeds cumulative expected revenue from a monthly customer at the same list price, despite the discount. By year three the gap is large enough to defend the discount in a board meeting. The compounding lower churn is what does it.
The formula is a stripped-down LTV approximation — revenue × (1 - churn)^t for each year t. It is not a substitute for a proper cohort revenue model, and it ignores expansion, contraction, and reactivation. But it is a defensible back-of-the-envelope that fits in one slide.
Mix dynamics over time
Snapshot mix is interesting; trending mix is actionable. The query below pivots the mix by acquisition month so you can see whether the share of new annual signups is climbing or sliding. The window expression normalizes each month independently so the percentages always sum to 100 within the month.
SELECT
DATE_TRUNC('month', subscription_start)::DATE AS month,
billing_interval,
COUNT(*) AS new_subs,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (
PARTITION BY DATE_TRUNC('month', subscription_start)
) AS pct
FROM subscriptions
WHERE subscription_start >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', subscription_start), billing_interval
ORDER BY month, billing_interval;Three patterns are worth watching. A steady climb in annual share usually means the pricing page is doing its job — the default toggle and the savings copy are converting. A sharp spike followed by regression often signals a one-off campaign that pulled forward annual conversions without changing the steady-state mix. A persistent decline almost always traces back to a pricing change, a competitor cheaper on monthly, or a sales motion that stopped pushing annual at the close.
A useful add-on is to layer in expansion and contraction events. Subscriptions that downgrade from annual to monthly at renewal are a classic early-warning signal for churn — the customer is hedging. Upgrades from monthly to annual are the opposite signal, and the conversion rate of that path (typically 5 to 15 percent in mid-market SaaS) is the single best diagnostic for customer success. Pair this with ARPU decomposition by paying rate when the conversation shifts to monetization.
Common pitfalls
The first trap is comparing annual list price against monthly list price without accounting for the discount. Annual is almost always sold at the discounted rate, so a naive comparison of MRR versus ARR divided by twelve systematically overstates annual revenue. The fix is to normalize to the actual paid amount per month, not the catalog price. Finance teams catch this the first time they audit the analytics dashboard.
The second trap is conflating churn period bases. Monthly churn is measured per month, annual churn per year, and the two are not directly comparable unless you convert one to the other. The right arithmetic conversion is 1 - (1 - monthly_rate)^12. Multiplying monthly churn by twelve over-estimates the annualized number because it ignores the surviving base, which makes monthly look catastrophically worse than reality. The churn calculation guide walks through the period-conversion math.
The third trap is ignoring the downgrade path. A customer who drops from annual to monthly at renewal looks healthy in a snapshot — they did not churn — but is statistically more likely to churn within the next two billing cycles than a steady-state monthly customer. Treating downgrades as a non-event hides the leading indicator. The fix is to flag downgrades as a separate event class and count them alongside churn in the executive summary.
The fourth trap is assuming annual ARPU equals monthly ARPU times twelve. That equality only holds when there is no discount, no annual-only feature bundle, and no plan-tier mix shift between the intervals. All three usually break. Annual customers tend to be on higher tiers, often unlock features at signup, and always pay the discounted rate. Report the comparison on a normalized per-month-paid basis with the discount disclosed alongside.
The fifth trap is deferred-revenue treatment. In GAAP finance, annual payments are recognized ratably across twelve months. In product analytics, the same amount is usually counted in full at signup — right for acquisition, wrong for tying back to finance reports. The convention I recommend: pick one method per dashboard, label it in the description, and never silently mix the two on the same chart.
Optimization tips
Most subscription tables are small enough that the queries above run instantly. The moment your subscriptions table crosses ten million rows, three things start to matter. First, partition by subscription_start monthly. Every mix dashboard is naturally bounded by a date range, and partition pruning collapses the scan from the full table to twelve months without changing the SQL.
Second, materialize the monthly aggregate. The mix dynamics query is the most-loaded query in any SaaS BI tool, identical across the CFO dashboard, the growth dashboard, and the board template. Push it into a nightly materialized view or a scheduled Snowflake table and load time drops from seconds to milliseconds. Refresh hourly for go-to-market, nightly for finance, weekly for the board.
Third, the EXTRACT(EPOCH FROM ...) pattern for tenure is portable but not free. On large tables, store tenure as a generated column or precompute it in a daily snapshot. The query then becomes a single column scan with no per-row arithmetic.
Related reading
- How to calculate ARPU in SQL — the paying-rate decomposition pairs naturally with billing-interval mix.
- How to calculate churn in SQL — seven churn recipes including the period-conversion math.
- SQL window functions interview questions — the
OVER (PARTITION BY ...)pattern from the mix-dynamics query, plus more.
If you want to drill SaaS SQL questions like this every day, NAILDD is launching with hundreds of analytics problems built exactly around this pattern — billing mix, churn, cohorts, and the decomposition queries finance asks for at quarter-end.
FAQ
What is a healthy annual mix for a SaaS business?
Top-quartile mid-market SaaS companies typically run 40 to 60 percent of MRR from annual contracts. SMB-focused tools usually land between 20 and 40 percent because the customer base is more price-sensitive and less willing to commit a full year up front. Enterprise products often sit above 70 percent because the procurement motion itself favors annual purchasing. The trend matters more than the absolute level.
What annual discount is standard?
The industry default is 15 to 20 percent off the equivalent twelve-month monthly price. Going lower than 10 percent fails to motivate the upgrade for most customers; going higher than 25 percent burns revenue faster than churn savings can compensate. The right discount depends on the monthly-versus-annual churn gap — the bigger the gap, the more discount you can defend.
My annual-to-monthly downgrades are climbing. What does that mean?
Downgrades almost always lead churn by one or two billing cycles. The customer is signaling that they are not confident enough in continued value to commit for another year, but they are not ready to leave entirely. Treat rising downgrade rate as an early warning, route those accounts to customer success, and run win-back outreach inside the first sixty days. Conversion on aggressive intervention in that window is usually higher than any other retention play in the playbook.
Are annual users really less likely to churn?
Yes — typically two to four times less likely than monthly users over the same calendar period. Some of the gap is real preference (annual customers self-select as more committed), some is structural lock-in (you cannot churn mid-contract without active intervention), and some is the discount sticking the customer to the plan. All three effects compound.
How do I run monthly-to-annual upsell campaigns?
The three plays that work most consistently: an in-app prompt at the moment the customer hits a usage milestone, account-based outreach for accounts that have been on monthly for more than six months, and a renewal-window offer with a sweetened first-year discount. Conversion rates of 5 to 15 percent are typical. Anything above 15 percent usually means you are leaving margin on the table by over-discounting.
Should I count annual MRR or ARR on the dashboard?
Pick one, label it, and stick with it. Most SaaS analytics teams report MRR as the normalized monthly cash run-rate — annual contracts get divided by twelve before they hit the chart. Finance reports often use ARR or contract value because that ties to bookings and renewal forecasting. The two are linear transformations of each other; the confusion is rarely in the math and almost always in unlabeled charts that mix conventions.