Dispute & Chargeback Volume by Category: Ranking Merchant Categories

Business Context

Pinnacle Bank is a mid-sized consumer and business bank headquartered in Atlanta, Georgia, issuing approximately 220,000 active credit and debit cards across retail and small business segments. The bank's Disputes & Fraud Operations team processes an average of 1,800 dispute cases per month, covering chargebacks raised by cardholders against merchants for reasons including unauthorized transactions, non-delivery of goods, billing errors, and subscription cancellations. Each chargeback case involves staff time, merchant communication, and potential financial write-offs — making it one of the most operationally expensive functions in the card business.

For Q4 2024 (October–December), the VP of Card Operations has requested a ranked breakdown of dispute and chargeback activity by merchant category (e.g., E-Commerce, Travel, Groceries, Restaurants). The goal is to identify which categories are generating the highest dispute volume and total chargeback value, so the team can prioritize merchant outreach, fraud detection tuning, and staffing allocation for Q1 2025.

The SQL Challenge

The disputes database stores one row per dispute case in a disputes table. Each dispute links to a transactions table that carries the merchant category. You will need to JOIN both tables, filter on the dispute filing date to isolate Q4 2024, apply GROUP BY on merchant category to aggregate dispute counts and chargeback amounts, and then use RANK() as a window function to rank categories from highest to lowest dispute volume. The final output should be ordered by rank so the highest-volume category appears first.

Your Role and Deliverable

You are a Data Analyst embedded in Pinnacle Bank's Card Operations team. The VP has asked for the category ranking before the Monday morning ops review. Write a SQL query that returns one row per merchant category showing merchant_category, dispute_count, total_chargeback_amount, avg_chargeback_amount, and dispute_rank — ranked by dispute count descending. The output will be pasted directly into the weekly ops dashboard.