How to calculate bad debt rate in SQL

Why bad debt rate matters to finance

Bad debt rate is the share of billed revenue a company will never collect — the slice written off once collections has exhausted every reasonable path. It is a direct hit to gross profit, and unlike most operating expenses, it lives outside the budget. At Stripe, Snowflake, and any company issuing invoices, this is the metric the CFO checks before the audit committee and auditors test before signing the 10-K.

Healthy ranges shift by book. Consumer SaaS on credit cards typically lands at 0.5 to 1.5 percent. B2B SMB invoicing on Net-30 runs 1 to 3 percent. Enterprise software with Fortune 1000 customers usually stays below 0.5 percent. A sharp move outside the band signals credit policy loosening, collections understaffing, or customer mix shifting toward higher-risk segments. The SQL below is what an analyst at Databricks or DoorDash ships when the controller wants an answer before tomorrow's leadership review.

The formula and what each piece means

The textbook formula is straightforward.

bad_debt_rate = total_written_off / total_revenue_billed

The numerator is the dollar amount of invoices the controller has formally removed from accounts receivable during the period. The denominator is the total invoiced revenue billed in the same window, before any write-offs. The ratio is typically reported as a percent and tracked monthly, quarterly, and annually.

Three subtle decisions live inside the formula. The period: write-offs and billings must align on the same window. The unit: dollars, not invoice counts — one enterprise account at fifty thousand dollars matters more than a hundred SMB accounts at five hundred each. The netting: gross write-off versus net of recoveries. Income statements report net; operational dashboards show gross; senior reviews ask for both.

The data model we will use

Assume two fact tables plus a customers dimension.

invoices            (invoice_id, customer_id, amount,
                     currency, billed_at, due_at, paid_at, status)
bad_debt_writeoffs  (writeoff_id, invoice_id, amount,
                     writeoff_date, reason)
bad_debt_recoveries (recovery_id, writeoff_id, amount, recovered_at)
customers           (customer_id, customer_name, segment,
                     credit_tier, signed_at)

status carries values like open, paid, written_off, disputed. amount is in USD after FX conversion upstream. bad_debt_writeoffs.amount matches the original invoice face value at write-off; partial write-offs get their own row. bad_debt_recoveries exists because collections agencies occasionally pull cash from accounts the company has already given up on, and net bad debt has to reflect that.

The base bad debt rate query

The first query every finance analyst writes is the headline rate for a single period, usually a calendar year.

WITH revenue AS (
    SELECT SUM(amount) AS total_billed
    FROM invoices
    WHERE billed_at >= DATE '2025-01-01'
      AND billed_at <  DATE '2026-01-01'
),
write_offs AS (
    SELECT SUM(amount) AS total_written_off
    FROM bad_debt_writeoffs
    WHERE writeoff_date >= DATE '2025-01-01'
      AND writeoff_date <  DATE '2026-01-01'
)
SELECT
    (SELECT total_billed FROM revenue)          AS billed,
    (SELECT total_written_off FROM write_offs)  AS written_off,
    ROUND(
        (SELECT total_written_off FROM write_offs)::NUMERIC * 100.0
        / NULLIF((SELECT total_billed FROM revenue), 0),
        3
    )                                           AS bad_debt_rate_pct;

Two details matter. The half-open date intervals prevent off-by-one issues. The NULLIF returns null rather than dividing by zero when the period has no billings. If you see four or eight percent on a typical B2B SaaS book, the first hypothesis is that one or both date filters are wrong. Reconcile billed against the income statement and written_off against the bad debt expense line before sharing.

Trending bad debt rate by month

The headline number is useful once a year. The chart finance actually monitors is bad debt rate by billing cohort over time, because it isolates whether credit policy is drifting.

SELECT
    DATE_TRUNC('month', i.billed_at)::DATE      AS billing_month,
    SUM(i.amount)                               AS revenue,
    SUM(i.amount) FILTER (WHERE i.status = 'written_off')
                                                AS written_off,
    ROUND(
        SUM(i.amount) FILTER (WHERE i.status = 'written_off')::NUMERIC
        * 100.0 / NULLIF(SUM(i.amount), 0),
        3
    )                                           AS bad_debt_pct
FROM invoices i
WHERE i.billed_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY DATE_TRUNC('month', i.billed_at)
ORDER BY billing_month;

The FILTER clause computes a conditional aggregate without an extra subquery and reads cleanly in code review. A rising trend almost always traces to one of three causes: credit policy loosened, collections capacity dropped, or customer mix shifted toward higher-risk segments. The segment cut below distinguishes the third from the first two.

One caveat. Recent cohorts always show artificially low rates because write-offs lag billing by six to twelve months — a March cohort has not had time to age into the 180-day bucket where most write-offs happen. Either truncate the chart to cohorts older than nine months, or overlay a maturity-adjusted rate using the historical share of write-offs by months since billing.

Bad debt rate by customer segment

A single number for the company average is fine for the board deck, but the operational question is always which segment is dragging the rate up. The query below cuts billings and write-offs by segment and ranks the worst offenders.

SELECT
    c.segment,
    COUNT(DISTINCT i.invoice_id)                AS invoices,
    SUM(i.amount)                               AS revenue,
    SUM(i.amount) FILTER (WHERE i.status = 'written_off')
                                                AS bad_debt,
    ROUND(
        SUM(i.amount) FILTER (WHERE i.status = 'written_off')::NUMERIC
        * 100.0 / NULLIF(SUM(i.amount), 0),
        3
    )                                           AS bad_debt_pct
FROM invoices i
JOIN customers c USING (customer_id)
WHERE i.billed_at >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY c.segment
HAVING COUNT(DISTINCT i.invoice_id) >= 100
ORDER BY bad_debt_pct DESC;

The HAVING clause filters out segments with fewer than a hundred invoices — five invoices and one write-off shows a twenty percent rate, which is statistically meaningless. Push the threshold higher for high-volume marketplaces.

Expected shape: SMB at 1 to 3 percent, mid-market at 0.5 to 1.5 percent, enterprise below 0.5 percent. When SMB breaches five percent, tighten credit scoring. When enterprise breaches one percent, the answer is account-level escalation, not policy change — that level points to a single large account in trouble. The same query grouped by customer_id produces the named list of accounts driving the segment number.

Net bad debt with recoveries

Gross write-off is what the operational dashboard tracks. The income statement reports bad debt expense net of recoveries — the cash collections pulls back from accounts the company already gave up on. A mature collections operation recovers ten to twenty percent of write-offs through agencies, settlements, and customers paying after a service cutoff.

WITH gross AS (
    SELECT
        DATE_TRUNC('quarter', writeoff_date)::DATE AS quarter,
        SUM(amount)                                AS gross_writeoff
    FROM bad_debt_writeoffs
    WHERE writeoff_date >= CURRENT_DATE - INTERVAL '2 years'
    GROUP BY DATE_TRUNC('quarter', writeoff_date)
),
recoveries AS (
    SELECT
        DATE_TRUNC('quarter', recovered_at)::DATE  AS quarter,
        SUM(amount)                                AS recovered
    FROM bad_debt_recoveries
    WHERE recovered_at >= CURRENT_DATE - INTERVAL '2 years'
    GROUP BY DATE_TRUNC('quarter', recovered_at)
)
SELECT
    g.quarter,
    g.gross_writeoff,
    COALESCE(r.recovered, 0)                       AS recovered,
    g.gross_writeoff - COALESCE(r.recovered, 0)    AS net_bad_debt,
    ROUND(
        COALESCE(r.recovered, 0)::NUMERIC * 100.0
        / NULLIF(g.gross_writeoff, 0),
        2
    )                                              AS recovery_rate_pct
FROM gross g
LEFT JOIN recoveries r USING (quarter)
ORDER BY g.quarter;

Recoveries attribute to the quarter the cash arrives, not the quarter the original write-off happened — that is how the income statement reads. The LEFT JOIN defaults to zero recoveries for quarters without activity, keeping the trend line continuous.

The recovery rate is the metric collections operations runs against. Under five percent usually means the company writes off too aggressively or never tried recovery; twenty percent and above is the upper end of mature operations on B2B SMB books. If your figure is below five percent and SMB write-offs are high, there is real money in tightening the recovery pipeline before tightening credit policy.

Common pitfalls

The most common mistake is conflating delinquent receivables with bad debt. A delinquent invoice is late but the company still expects payment. A bad debt invoice has been formally written off — the controller has concluded collection is improbable and removed the asset from the balance sheet. Reporting delinquent dollars as bad debt inflates the rate and breaks reconciliation to the income statement. Bad debt rate uses bad_debt_writeoffs; AR aging uses invoices with paid_at IS NULL. Different tables, different metrics.

The second trap is mixing periods. Dividing this year's write-offs by this year's billings double-counts old debts written off this year against billings from years ago. For an annual headline the mismatch is small; for a monthly chart it produces nonsense. Cohort-based bucketing — bin write-offs by their original billing month — is the only honest way to chart the trend.

The third trap is reporting counts instead of dollars. The collections team's task dashboard can show invoice counts because each invoice is a task. The metric for finance leadership must be dollar-weighted because risk concentrates. "A hundred and one cases this month" tells you nothing about exposure; "seventy-five thousand dollars written off, sixty of which was one account" tells you exactly what happened.

The fourth trap is computing one rate across all customers. New customers without payment history have materially higher write-off rates than customers two years into the relationship. Segment by tenure: customers in their first ninety days, three to twelve months, and one year plus. The first bucket will be three to five times higher than the third, and that is fine — knowing the gap helps price the new-customer cohort.

The fifth trap is ignoring policy changes. When the controller shifts the write-off threshold from one hundred eighty days to one hundred twenty days, every chart spikes — not because credit deteriorated but because the definition tightened. Keep a policy-change log in a small accounting_policy_changes table and flag any cross-period comparisons that span a definitional change.

Optimization tips

For warehouses with tens of millions of invoices, the queries benefit from two indexes. A partial index on invoices(billed_at) filtered to WHERE status = 'written_off' collapses the FILTER subquery to a tiny scan. A standard index on bad_debt_writeoffs(writeoff_date) makes the quarterly aggregation fast. In Snowflake, cluster on the relevant date column; in BigQuery, partition by month on billed_at and writeoff_date.

Most finance dashboards do not need real-time bad debt — daily refresh is plenty. Schedule the segment and trend queries nightly to a bad_debt_daily table; dashboards read the materialized result in milliseconds.

Encode the metric in dbt rather than ad hoc SQL. A single fct_bad_debt_rate model owns the bucketing, recovery netting, and segment cut. Every dashboard, Slack alert, and board pack reads from it. The moment finance, risk, and operations are looking at three different rates because each owns its own SQL, the metric has lost its meaning.

Related reading

• How to calculate AR aging in SQL
• How to calculate churn in SQL
• How to calculate ARR in SQL
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

What counts as a healthy bad debt rate?

For consumer SaaS billed on credit cards, half a percent to one and a half percent is normal. For B2B SMB on Net-30 invoicing, expect one to three percent. For enterprise software with Fortune 1000 customers, anything above half a percent is unusual and points to one or two large troubled accounts rather than a systemic issue. The right anchor for your business is your own three-year baseline, not an industry benchmark — your customer mix will dominate the comparison anyway.

What is the difference between bad debt and an allowance for doubtful accounts?

Bad debt is an actual write-off — the invoice has been removed from receivables because collection is no longer expected. The allowance for doubtful accounts is an estimate of future write-offs, sitting as a contra-asset against AR on the balance sheet. The allowance is forward-looking; bad debt expense is the change in the allowance plus actual write-offs net of recoveries. Auditors test both. Forecasting the allowance well requires segment-by-aging analysis on top of the patterns here.

When should an invoice be written off?

US GAAP does not specify a fixed calendar threshold — the trigger is that collection is probable not to occur. In practice, most B2B SaaS companies write off after one hundred eighty days past due, a final notice, and a collections agency declining the account. Consumer companies on credit-card billing typically write off after one hundred twenty days. Whatever your policy, document it, apply it consistently, and log changes. Inconsistent application is the fastest path to an audit finding.

Can bad debt be zero?

Only at the cost of revenue. Zero bad debt requires either prepaid-only billing or credit screening so strict you turn away customers who would have paid. The right level balances the marginal write-off against the marginal customer the screening would have rejected. A team bragging about zero bad debt is almost certainly leaving growth on the table.

How should recoveries be tracked?

Maintain a bad_debt_recoveries table keyed to the original write-off, with date, amount, and source — internal collections, external agency, customer reactivation, or legal settlement. Net bad debt is gross write-off minus recovery, attributed by the period the cash arrives. Recovery rate by source tells you whether the external agency is earning its fee; rate by reason tells you whether write-offs are happening too aggressively.

How does bad debt rate connect to DSO and AR aging?

These three form the standard collections triangle. AR aging shows the current distribution of unpaid receivables by age. DSO summarizes that distribution into one number. Bad debt rate is the realized loss after collections finishes its work. Aging predicts risk, DSO tracks pace, bad debt closes the loop. Watching one in isolation hides the others.