May 18, 2026·12 min read

How to calculate fraud rate in SQL

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

Contents:

What fraud rate is and why payments leadership watches it

Picture the head of risk at Stripe pinging you on a Monday morning. "Our fraud rate ticked up to 0.4 percent on cards-not-present this weekend — can you cut it by issuing country and account age before the noon standup?" Card networks set the alarm bells lower than most outsiders realize. Visa's VAMP program flags a merchant once fraud crosses 0.65 percent of authorizations, and Mastercard's Excessive Fraud Merchant program kicks in at 0.5 percent of e-commerce volume. Cross those lines and the acquirer is on the phone within days.

Fraud rate is the share of transactions confirmed as unauthorized after investigation, chargeback resolution, or manual review. It is not the same number as disputes — many disputes are friendly fraud, buyer's remorse, or shipping issues that the merchant later wins on representment. A clean fraud rate counts only the cases where the cardholder did not authorize the payment and the merchant or issuer absorbs the loss.

The reason this metric shows up in interviews at Stripe, Airbnb, Uber, and DoorDash is that the naive calculation distorts in several directions at once. Counting open cases as fraud overstates last month. Counting only closed cases understates this month because labels arrive late. Ignoring false positives makes strict rules look great while quietly killing revenue.

The formula and the data model

Fraud rate by count is fraud transactions divided by total transactions over a window. Fraud rate by value is fraud volume divided by total volume. Card brands care most about count because the dispute machinery is per-transaction; finance teams care about value because that is the number that hits the income statement.

Fraud rate (count) = fraud_transactions / total_transactions
Fraud rate (value) = fraud_volume / total_volume

The data model you need is a transactions table with one row per authorized attempt. Each row carries an amount, a transaction date, a status, and a confirmed-fraud flag that flips true only after investigation closes. For precision and recall you also need a model output column flagged at decision time. A common mistake is to treat the disputes table as ground truth. Disputes is a superset of fraud. Always pull fraud from closed cases or from disputes filtered to fraud-confirmed reason codes, not from raw dispute counts.

Base SQL by count and by value

Here is the monthly base calculation. It uses FILTER (WHERE ...) which is supported in Postgres, Snowflake, and Databricks. If your warehouse does not have FILTER, swap in COUNT(CASE WHEN fraud_confirmed THEN 1 END) or BigQuery's COUNTIF.

SELECT
    DATE_TRUNC('month', transaction_date)                        AS month,
    COUNT(*)                                                     AS transactions,
    SUM(amount)                                                  AS volume,
    COUNT(*) FILTER (WHERE fraud_confirmed)                      AS fraud_count,
    SUM(amount) FILTER (WHERE fraud_confirmed)                   AS fraud_volume,
    COUNT(*) FILTER (WHERE fraud_confirmed)::NUMERIC * 100
        / NULLIF(COUNT(*), 0)                                    AS fraud_rate_count_pct,
    SUM(amount) FILTER (WHERE fraud_confirmed) * 100.0
        / NULLIF(SUM(amount), 0)                                 AS fraud_rate_value_pct
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
  AND status = 'approved'
GROUP BY 1
ORDER BY 1;

The window keys off the authorization date, not the chargeback date, because card brands score you against the month the transaction happened. The status filter keeps declined attempts out of the denominator. The ::NUMERIC cast forces floating point division, and NULLIF(..., 0) protects against divide-by-zero.

Look at the gap between count rate and value rate. If value rate is much higher than count rate, fraud is hitting your high-ticket transactions and you need amount-based rules. If count rate is higher than value rate, you are bleeding on small-ticket fraud and the per-dispute fee plus operational cost may be the bigger problem than the loss itself.

Precision, recall, and the model scorecard

Once a fraud detection model is making decisions in production, you need to grade it on the same data window. The four cells of the confusion matrix translate directly into SQL with conditional aggregation.

SELECT
    COUNT(*) FILTER (WHERE flagged AND fraud_confirmed)              AS true_positive,
    COUNT(*) FILTER (WHERE flagged AND NOT fraud_confirmed)          AS false_positive,
    COUNT(*) FILTER (WHERE NOT flagged AND NOT fraud_confirmed)      AS true_negative,
    COUNT(*) FILTER (WHERE NOT flagged AND fraud_confirmed)          AS false_negative,
    COUNT(*) FILTER (WHERE flagged AND fraud_confirmed)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE flagged), 0)                 AS precision_pct,
    COUNT(*) FILTER (WHERE flagged AND fraud_confirmed)::NUMERIC
        / NULLIF(COUNT(*) FILTER (WHERE fraud_confirmed), 0)         AS recall_pct
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '30 days'
  AND transaction_date <  CURRENT_DATE - INTERVAL '14 days';

The window ends fourteen days before today because confirmed-fraud labels arrive late — chargebacks land up to 120 days after the transaction. Score the model on the last 30 days ending today and recall looks artificially low because half the fraud has not been labelled yet. Precision answers "of the transactions we flagged, what share were actually fraud." Recall answers "of all the actual fraud, what share did we catch." A model with 90 percent precision and 30 percent recall is a sniper. A model with 30 percent precision and 90 percent recall is a dragnet. The right balance depends on the cost of a false positive versus a false negative.

The trade-off with approval rate

Tightening fraud rules almost always pushes down approval rate. The question for the analyst is whether a rule change is moving fraud rate or just moving the decline reason from fraud to approval.

SELECT
    DATE_TRUNC('week', transaction_date)                           AS week,
    COUNT(*)                                                       AS attempts,
    COUNT(*) FILTER (WHERE status = 'approved')::NUMERIC * 100
        / NULLIF(COUNT(*), 0)                                      AS approval_pct,
    COUNT(*) FILTER (WHERE status = 'declined'
                       AND decline_reason = 'fraud')::NUMERIC * 100
        / NULLIF(COUNT(*), 0)                                      AS fraud_decline_pct,
    COUNT(*) FILTER (WHERE fraud_confirmed)::NUMERIC * 100
        / NULLIF(COUNT(*) FILTER (WHERE status = 'approved'), 0)   AS fraud_rate_pct
FROM payment_attempts
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 weeks'
GROUP BY 1
ORDER BY 1;

Read the three percentages together. If a stricter rule ships and the fraud-decline percentage rises while the realized fraud rate stays flat, the rule is rejecting legitimate customers without removing actual fraud. If fraud-decline rises and fraud rate drops proportionally, the rule is working. If fraud-decline drops but fraud rate rises, the rule got softer or fraudsters adapted. This three-metric scorecard is also the most common interview question in the space — given an A/B test of two fraud rule sets, which would you ship? The answer is never a single number.

Sharpen SQL for your next interview
500+ SQL problems with worked solutions — joins, window functions, CTEs.
Join the waitlist

Segment cuts that catch real exposure

A 0.2 percent global fraud rate can hide a 5 percent fraud rate on a single segment. The job of the analyst is to find that segment before it triggers VAMP review.

SELECT
    customer_country,
    CASE
        WHEN customer_age_days <  7   THEN '0_0-6d'
        WHEN customer_age_days <  30  THEN '1_7-29d'
        WHEN customer_age_days <  90  THEN '2_30-89d'
        WHEN customer_age_days <  365 THEN '3_90-364d'
        ELSE                               '4_365d+'
    END                                                          AS age_bucket,
    COUNT(*)                                                     AS transactions,
    COUNT(*) FILTER (WHERE fraud_confirmed)::NUMERIC * 100
        / NULLIF(COUNT(*), 0)                                    AS fraud_rate_pct
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
  AND status = 'approved'
GROUP BY customer_country, age_bucket
HAVING COUNT(*) >= 100
ORDER BY fraud_rate_pct DESC
LIMIT 20;

The HAVING COUNT(*) >= 100 filter throws out segments too small to be meaningful — a 50 percent fraud rate on 4 transactions is noise. New accounts within their first week consistently show fraud rates an order of magnitude above accounts older than a year. In a marketplace context, swap customer_country for merchant_id. Marketplaces almost always have a long tail of sellers that act as fraud magnets, and a few of them onboarding can double the global fraud rate in a week.

Net loss after recovery

Gross fraud loss is what hit you at the moment of the unauthorized transaction. Net fraud loss is what stays on the books after recovery through dispute representment or card-brand recovery programs. The two numbers can diverge by 30 to 50 percent and finance leadership wants the net.

SELECT
    DATE_TRUNC('month', transaction_date)                        AS month,
    SUM(amount) FILTER (WHERE fraud_confirmed)                   AS gross_fraud_loss,
    SUM(amount) FILTER (WHERE fraud_confirmed AND recovered)     AS recovered,
    SUM(amount) FILTER (WHERE fraud_confirmed)
        - SUM(amount) FILTER (WHERE fraud_confirmed AND recovered) AS net_fraud_loss,
    COUNT(*) FILTER (WHERE fraud_confirmed AND recovered)::NUMERIC * 100
        / NULLIF(COUNT(*) FILTER (WHERE fraud_confirmed), 0)     AS recovery_rate_pct
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

Card-present fraud recovers in the 30 to 50 percent range because issuers often take the loss under zero-liability policies. Card-not-present e-commerce fraud recovers in the 5 to 15 percent range because chargeback rules favor the cardholder. Quoting a single recovery rate without separating these channels misleads.

Common pitfalls

When teams calculate fraud rate for the first time, the most common mistake is to conflate disputes with fraud. Disputes are filed by cardholders for many reasons — buyer's remorse, undelivered goods, forgotten subscription, family member used the card. Only a subset are true unauthorized fraud. Pulling fraud from the disputes table without filtering to fraud reason codes and closed-in-cardholder-favor cases overstates the rate by two to three times. The fix is a fraud-confirmed flag set only by the resolution workflow.

A second trap is reporting current-month fraud rate before labels mature. Chargebacks arrive 30 to 120 days after the transaction. The fix is to lag the reporting window — never report fraud rate for transactions less than 14 days old — or to publish two numbers side by side: confirmed and projected.

A third pitfall is ignoring false positives. A risk team that drops fraud rate from 0.3 to 0.1 percent by tightening rules looks heroic on the dashboard. If they also dropped approval rate from 92 to 86 percent on legitimate traffic, the company lost far more revenue than the fraud prevention saved. Always report the trio together and translate each into dollars so leadership can see the trade.

A fourth mistake is global-only reporting that hides bad segments. A clean 0.25 percent global rate can contain a 4 percent fraud rate on first-week accounts from a specific country pair, and that segment is what triggers card-brand monitoring. The fix is a segment scan with a minimum sample size that alerts when any qualifying segment crosses threshold.

Optimization tips

The transactions table at any payments company is the largest table in the warehouse. The first optimization is partition pruning. Partition the transactions table by transaction_date so the warehouse only reads the partitions in the window. This alone takes a fraud-rate query from minutes to seconds.

The second optimization is a materialized fraud-rate rollup at daily granularity, grouped by day, country, age bucket, and merchant. Refresh on the same schedule as label updates — an hourly job for the last 7 days plus a daily job for the last 120 days to pick up late chargebacks. The third optimization: land model scoring decisions in the transactions table at write time with a model_flagged_at_decision column, so the precision/recall query becomes a single-table scan.

If you want to drill payments and risk SQL questions like this every day, NAILDD is launching with 500+ SQL problems built around exactly this pattern.

FAQ

What fraud rate is acceptable?

Below 0.1 percent of approved e-commerce volume is excellent. The 0.1 to 0.3 percent range is typical for healthy merchants in low-risk categories like subscription SaaS. Above 0.5 percent puts you in the Mastercard EFM zone and above 0.65 percent triggers Visa VAMP. Thresholds drop further for recurring billing and high-risk verticals like crypto and travel.

How is fraud different from chargeback?

A chargeback is a dispute filed by a cardholder — a process step. Fraud is the underlying classification: was the transaction unauthorized. Most chargebacks are not fraud; they include friendly fraud, merchant errors, and forgotten subscription cancellations. Most confirmed fraud does generate a chargeback, but not all does. Compute the two metrics from different sources: chargeback rate from the dispute table, fraud rate from the closed-investigation flag.

How do I tune the precision-recall trade-off?

Plot the precision-recall curve across decision thresholds and overlay the dollar cost of false positives and false negatives. A false positive costs lost margin plus support cost plus a small probability of permanent churn. A false negative costs the fraud loss minus recovery, plus the per-dispute fee, plus card-brand visibility cost. Pick the threshold where marginal cost of FP equals marginal cost of FN, and re-evaluate quarterly.

Why does my fraud rate keep changing for old months?

Confirmed-fraud labels arrive late. A chargeback can post 30 to 120 days after the transaction. The fraud rate for any month should be considered preliminary until 120 days after month end, and final at 180 days. Lock the metric in finance reporting at a fixed lag — typically 60 or 90 days — and footnote the preliminary number for current periods.

Should I report fraud rate by count or by value?

Both, side by side. Card brands run monitoring programs on count, so count rate determines whether you stay clear of VAMP and EFM. Finance teams care about value because that is what hits the income statement. The most useful dashboard puts count rate, value rate, and the ratio between them on the same row with a sparkline so the reader can see whether the gap is widening.

Related reading