MediumAggregations

Merchant Fraud & Chargeback Ratio Anomaly Alert

Frequently Asked By:StripeCoinbase

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Payment networks monitor merchant dispute rates. Write a SQL query to calculate the total processed volume, chargeback count, and chargeback-to-transaction ratio for each merchant with at least 50 total transactions. Flag merchants whose chargeback ratio exceeds 1.5% (the Visa dispute monitoring threshold).

Why Technical Interviewers Ask This

Risk and compliance teams at Stripe and payment gateways deal with fraud metrics daily. This problem evaluates your ability to combine group aggregations with business compliance thresholds and boolean alert flags.

Common Pitfalls & Interview Traps

Filtering by transaction count using WHERE instead of HAVING after the GROUP BY.

Calculating chargeback ratio on dollar volume rather than transaction count when the specification asks for dispute frequency.

Failing to convert ratios into percentages with appropriate precision.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

SELECT
  merchant_id,
  COUNT(*) AS total_transactions,
  SUM(amount) AS total_processed_volume,
  COUNT(CASE WHEN status = 'chargeback' THEN 1 END) AS chargeback_count,
  ROUND(
    (COUNT(CASE WHEN status = 'chargeback' THEN 1 END) * 100.0) / COUNT(*),
    3
  ) AS chargeback_ratio_pct,
  CASE
    WHEN (COUNT(CASE WHEN status = 'chargeback' THEN 1 END) * 100.0) / COUNT(*) > 1.5 THEN 'CRITICAL_RISK'
    WHEN (COUNT(CASE WHEN status = 'chargeback' THEN 1 END) * 100.0) / COUNT(*) > 0.9 THEN 'ELEVATED_WATCH'
    ELSE 'HEALTHY'
  END AS compliance_status
FROM merchant_transactions
GROUP BY merchant_id
HAVING COUNT(*) >= 50
ORDER BY chargeback_ratio_pct DESC;

Approach Breakdown

We group transactions by merchant_id and use conditional COUNT to tally disputes. The HAVING clause enforces the sample size threshold (>= 50 transactions), and a CASE expression labels high-risk merchants.

Edge Cases to Verbally Clarify with Your Interviewer

Merchants with zero disputes (returns 0.000% without dividing by zero).

Merchants with under 50 transactions (filtered by HAVING to avoid false positives on small sample sizes).

Frequently Asked Follow-Up Questions

Why is HAVING required instead of WHERE here?

WHERE filters individual records before aggregation takes place, whereas HAVING filters aggregated groups after the GROUP BY calculation. Since `total_transactions >= 50` is a property of the merchant group, it must be placed in the HAVING clause.