How to calculate chargeback rate in SQL

What chargeback rate is and why payments leadership panics about it

Imagine the head of risk at Stripe pings you on a Tuesday morning. "Our chargeback rate is creeping toward 0.9 percent in two of our top BIN ranges — can you give me the slice by reason code before the card brand review on Friday?" Visa and Mastercard run merchant monitoring programs that flip into enforcement at 1.0 percent and again at 1.5 percent, and Mastercard's Excessive Chargeback Program can suspend processing for a merchant that crosses the line for two consecutive months.

Chargeback rate is the share of card transactions reversed by the issuing bank after the cardholder disputes the charge. The dispute lands in the processor inbox days or weeks after the sale, and the cost is the refunded amount plus a per-dispute fee of fifteen to twenty-five dollars plus long-tail reputational cost with the card networks. Above one percent the merchant gets placed in monitoring. Above two percent the acquiring bank can pull the merchant account — an existential event for any business whose checkout depends on cards.

The reason this metric shows up in payments interviews at Stripe, Airbnb, and DoorDash is that the naive calculation lies in several directions. Counting chargebacks against the month they posted hides a problem from six weeks ago. Counting only fraud codes makes the rate look better than the card brands measure it. Treating open disputes as zero understates the forecast. This post walks through the SQL — base rate by count and value, reason code analysis, the fraud versus friendly split, time-to-chargeback, and the pitfalls a senior analyst is expected to catch.

The formula and the data model

The math is one of the simplest in payments analytics. Chargeback rate is the share of transactions disputed and reversed.

chargeback_rate_count = chargebacks / total_transactions
chargeback_rate_value = chargeback_amount / total_transaction_amount

Track both. Visa's Dispute Monitoring Program uses the count version. Internal forecasting cares more about the value version, because a thousand-dollar dispute hurts the P&L more than ten one-dollar disputes. A healthy benchmark for a card-not-present US merchant is 0.2 to 0.6 percent by count. Below 0.2 is excellent. Above 0.9 is a red light that the card brand review is coming.

The data model is a transactions fact table with a chargeback flag, plus a chargebacks dimension capturing dispute metadata. Most payments warehouses converge on this shape after flattening processor webhooks from Stripe, Adyen, or Braintree.

transactions (
    transaction_id,
    user_id,
    transaction_date,        -- date the original sale settled
    amount,
    currency,
    card_network,            -- visa, mastercard, amex, discover
    bin,                     -- first six digits of the card
    merchant_category_code,
    chargedback              -- boolean, set true when a dispute lands
)

chargebacks (
    chargeback_id,
    transaction_id,
    chargeback_date,         -- date the dispute was filed
    chargeback_reason_code,  -- raw network code, e.g. 4837, 10.4
    chargeback_status,       -- open, lost, won
    chargeback_amount
)

Two tables, one join, the rest is grouping and filtering.

Base SQL by count and by value

The first query a payments interviewer wants is the monthly rate by both count and value, attributed to the month of the original sale. This is the cohort-correct version — it tells you when the bad transactions actually happened.

SELECT
    DATE_TRUNC('month', transaction_date) AS sale_month,
    COUNT(*) AS total_transactions,
    SUM(amount) AS total_volume,
    COUNT(*) FILTER (WHERE chargedback) AS chargebacks,
    SUM(amount) FILTER (WHERE chargedback) AS chargeback_volume,
    ROUND(
        COUNT(*) FILTER (WHERE chargedback)::NUMERIC * 100
        / NULLIF(COUNT(*), 0),
        3
    ) AS cb_rate_count_pct,
    ROUND(
        SUM(amount) FILTER (WHERE chargedback) * 100.0
        / NULLIF(SUM(amount), 0),
        3
    ) AS cb_rate_value_pct
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

The FILTER clause is the cleanest way to count a conditional subset in Postgres, Snowflake, and Databricks. In MySQL the pattern is SUM(chargedback = TRUE). The NULLIF guard is not paranoia — early-month buckets sometimes return zero rows after a backfill, and divide-by-zero in a monitoring dashboard wakes up the on-call analyst. Rounding to three decimals matters because the card brand threshold lives at the third decimal — 0.987 percent and 1.012 percent are very different rates.

A daily-grain version of the same query, joined to a rolling thirty-day window, is what most payments teams actually monitor, because Visa and Mastercard monitor on a rolling basis rather than calendar months.

Reason code splits and the fraud versus friendly cut

The most useful slice after the headline number is reason code distribution. Visa uses codes like 4837 for fraud and 4855 for goods not received. The shape of the mix tells you which lever to pull. A spike in 4837 means the fraud model is letting through stolen cards. A spike in 4855 means fulfillment broke. A spike in 4863 means the subscription cancellation flow is broken and customers reach for the dispute button instead.

SELECT
    chargeback_reason_code,
    COUNT(*) AS chargebacks,
    SUM(chargeback_amount) AS chargeback_value,
    ROUND(
        COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER (),
        2
    ) AS share_pct,
    ROUND(
        SUM(chargeback_amount) * 100.0 / SUM(SUM(chargeback_amount)) OVER (),
        2
    ) AS share_value_pct
FROM chargebacks
WHERE chargeback_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY chargeback_reason_code
ORDER BY chargebacks DESC;

The window function over an empty OVER () partition computes the grand total without a self-join, and works in every analytical engine — Postgres, Snowflake, BigQuery, Redshift, Databricks. The SQL window functions interview questions post covers several variations.

The next cut analysts get asked for is fraud versus friendly fraud. Real fraud means a stolen card. Friendly fraud means the cardholder did authorize the transaction, used the product, and disputed the charge because the bank dispute process is easier than asking the merchant for a refund. Visa and Mastercard data puts friendly fraud at fifty to seventy percent of dispute volume for digital merchants.

SELECT
    CASE
        WHEN chargeback_reason_code IN ('4837', '4849', '4870', '10.4', '10.5')
            THEN 'Fraud'
        WHEN chargeback_reason_code IN ('4855', '4853', '4863', '13.1', '13.5', '13.6')
            THEN 'Friendly / Process'
        ELSE 'Other'
    END AS category,
    COUNT(*) AS chargebacks,
    SUM(chargeback_amount) AS chargeback_value,
    ROUND(
        COUNT(*)::NUMERIC * 100 / SUM(COUNT(*)) OVER (),
        2
    ) AS share_pct
FROM chargebacks
WHERE chargeback_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY 1
ORDER BY chargebacks DESC;

The action from this split differs by branch. Fraud-heavy mixes call for tighter 3DS rules, higher velocity limits, and re-tuning the fraud model. Friendly-fraud-heavy mixes call for clearer billing descriptors and easier cancellation flows — mostly UX, not risk.

Time to chargeback and trend monitoring

The third query is the distribution of days between sale and dispute. Card networks allow disputes up to 120 days, sometimes 540 days for specific codes, but most land in the first thirty to sixty. The curve lets you forecast — if a sale month is at 0.4 percent today but forty percent of disputes are still in flight, the eventual rate is closer to 0.7 percent.

SELECT
    AVG(EXTRACT(EPOCH FROM (cb.chargeback_date - t.transaction_date)) / 86400)
        AS avg_days_to_cb,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (cb.chargeback_date - t.transaction_date)) / 86400
    ) AS median_days,
    PERCENTILE_CONT(0.9) WITHIN GROUP (
        ORDER BY EXTRACT(EPOCH FROM (cb.chargeback_date - t.transaction_date)) / 86400
    ) AS p90_days
FROM chargebacks cb
JOIN transactions t ON cb.transaction_id = t.transaction_id
WHERE cb.chargeback_date >= CURRENT_DATE - INTERVAL '180 days';

The trend monitoring query closes the loop. It buckets cohort-month rates into status bands aligned with the card brand thresholds, so the table doubles as an executive dashboard.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', transaction_date) AS cohort_month,
        COUNT(*) AS transactions,
        COUNT(*) FILTER (WHERE chargedback) AS chargebacks,
        COUNT(*) FILTER (WHERE chargedback)::NUMERIC * 100
            / NULLIF(COUNT(*), 0) AS cb_rate_pct
    FROM transactions
    WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
    GROUP BY 1
)
SELECT
    cohort_month,
    transactions,
    chargebacks,
    ROUND(cb_rate_pct, 3) AS cb_rate_pct,
    CASE
        WHEN cb_rate_pct < 0.5 THEN 'GOOD'
        WHEN cb_rate_pct < 0.9 THEN 'WATCH'
        WHEN cb_rate_pct < 1.5 THEN 'WARNING — Visa monitoring band'
        ELSE 'CRITICAL — account at risk'
    END AS status
FROM monthly
ORDER BY cohort_month;

Common pitfalls

The first pitfall, the one that quietly destroys risk reporting, is attributing chargebacks to the month they posted rather than the month of the original sale. A dispute filed today for a sale from sixty days ago belongs in the sale cohort. Teams who get this wrong see a smoothed trend that obscures a spike from two months ago. The fix is to always join on transaction_id and bucket by transaction_date.

The second pitfall is reporting only the count rate. Visa monitoring is count-based, but value-based reporting is where the P&L impact lives. A team reporting count rate at 0.4 percent looks healthy until someone notices the value rate is at 1.8 percent because disputed transactions skew toward larger orders. Both numbers belong in the same table.

The third pitfall is ignoring open disputes in the forecast. An open dispute is overwhelmingly likely to resolve as a chargeback within thirty days, because issuer banks rarely close disputes in the merchant's favor without intervention. Treating opens as zero produces a number better than reality. The fix is to include chargeback_status IN ('open', 'lost') for forecasting.

The fourth pitfall is removing won disputes from the rate. A dispute the merchant wins is still counted by Visa and Mastercard against the monitoring rate. The money comes back, the fee is sometimes refunded, but the dispute event stays on the record. Filtering out won disputes under-reports the rate compared to the card brand view.

The fifth pitfall is failing to account for 3D Secure liability shift. Transactions authenticated through 3DS carry fraud liability with the issuing bank, so fraud chargebacks on those get charged to the issuer. A merchant that recently rolled out 3DS will see fraud-category rate drop while friendly-fraud rate stays flat. The cleanest reporting separates 3DS-protected from unprotected volume.

Optimization tips

The transactions table at any serious payments company is enormous — Stripe processes hundreds of millions of charges per quarter. Three optimizations matter. First, partition by transaction_date at month or day grain so every query above prunes to a fraction of the data. Second, add a composite index on (chargedback, transaction_date), or in a columnar warehouse, sort keys on the same columns. Third, materialize the monthly rollup as an incrementally refreshed table — daily and rolling variations derive from the same aggregate.

For Snowflake or Databricks the wins come from clustering keys rather than indexes — cluster transactions on transaction_date and chargebacks on chargeback_date. Avoid SELECT * against the fact table.

Related reading

• How to calculate approval rate in SQL
• How to calculate bad debt rate in SQL
• SQL window functions interview questions
• A/B testing peeking mistake

If you want to drill payments SQL questions like this daily, NAILDD is launching with 500+ problems from real fintech and marketplace interviews.

FAQ

What chargeback rate is considered acceptable?

Below 0.5 percent by count is excellent for a card-not-present US merchant. Between 0.5 and 0.9 percent is the watch band where most healthy e-commerce merchants live. Above 0.9 percent the merchant is approaching Visa's Dispute Monitoring Program threshold of 1.0 percent, which triggers fine schedules and remediation. Above 1.5 percent Mastercard's Excessive Chargeback Program enforcement kicks in, and above 2.0 percent the acquiring bank can terminate the merchant account, ending card processing entirely.

How do I reduce my chargeback rate?

The interventions split along the fraud versus friendly axis. For fraud-driven disputes, tighten the fraud rules engine, enable 3D Secure on high-risk BIN ranges, raise velocity limits on rapid repeat attempts, and review risk score thresholds with the fraud team. For friendly-fraud disputes, the levers are user experience — a clearer billing descriptor so customers recognize the charge, an easy refund flow that competes with the dispute button, proactive email confirmations, and a frictionless subscription cancellation flow. The biggest single win for digital merchants is usually the billing descriptor change.

What is friendly fraud and how big a share is it?

Friendly fraud is a dispute filed by a cardholder who actually did authorize the transaction but claims otherwise because the bank dispute process is easier than asking the merchant for a refund. The cardholder might forget a recurring subscription, dispute on behalf of a family member, or decide after the fact they did not get value. Visa data puts friendly fraud at fifty to seventy percent of dispute volume for digital and subscription merchants, and the share has grown every year since 2020.

What is the win rate on disputed chargebacks?

Industry benchmarks put the win rate on actively contested disputes at thirty-five to forty percent across digital merchants, though the number varies by reason code and evidence quality. Goods-not-received disputes are easier to win with shipment tracking. Fraud-coded disputes on 3DS-protected transactions are easy wins because liability has shifted. Subscription-cancellation disputes are hard to win without explicit cancellation evidence. A clean evidence package — order confirmation, shipment tracking, communication logs, IP and device data — moves a team from twenty percent to forty.

How much does each chargeback actually cost?

The direct cost is fifteen to twenty-five dollars in processor fees on top of the reversed amount, depending on processor and risk tier. The indirect cost is larger — every chargeback raises the merchant's risk profile with the acquirer, leading to higher processing fees, reserve requirements against future volume, and in extreme cases account termination. For a high-volume merchant, a sustained ten basis point increase can translate into seven-figure annual cost.