Stripe's risk team wants to identify merchants with unusually high payment dispute exposure. For each merchant, analyze successful card payments created during January 2025. Count the total successful payments and the number of distinct payments that received at least one dispute within 30 days of the payment date. Return only merchants with at least 3 successful payments and a dispute rate greater than 20%. Return the columns in this order: `merchant_id`, `successful_payments`, `disputed_payments`, and `dispute_rate`. Sort the results by `merchant_id` in **descending** order. Calculate `dispute_rate` as disputed payments divided by successful payments, rounded to four decimal places. A payment with multiple dispute records must be counted only once. ### Table Schema | Table | Column | Type | |---|---|---| | `card_payments` | `payment_id` | integer | | `card_payments` | `merchant_id` | integer | | `card_payments` | `payment_status` | varchar(20) | | `card_payments` | `created_at` | timestamp | | `card_payments` | `amount` | decimal(10,2) | | `payment_disputes` | `dispute_id` | integer | | `payment_disputes` | `payment_id` | integer | | `payment_disputes` | `disputed_at` | timestamp | | `payment_disputes` | `reason` | varchar(40) | ### Example Input: `card_payments` | payment_id | merchant_id | payment_status | created_at | amount | |---:|---:|---|---|---:| | 1 | 101 | succeeded | 2025-01-03 10:00:00 | 100.00 | | 2 | 101 | succeeded | 2025-01-10 11:00:00 | 50.00 | | 3 | 101 | succeeded | 2025-01-20 12:00:00 | 75.00 | | 4 | 101 | failed | 2025-01-21 12:00:00 | 25.00 | | 5 | 202 | succeeded | 2025-01-05 09:00:00 | 200.00 | | 6 | 202 | succeeded | 2025-01-15 09:00:00 | 150.00 | | 7 | 202 | succeeded | 2025-01-25 09:00:00 | 125.00 | ### Example Input: `payment_disputes` | dispute_id | payment_id | disputed_at | reason | |---:|---:|---|---| | 11 | 1 | 2025-01-15 10:00:00 | fraud | | 12 | 5 | 2025-01-20 09:00:00 | duplicate | ### Example Output Explanation Merchant 101 has three successful payments and one disputed payment, resulting in a dispute rate of 0.3333, so it qualifies. Merchant 202 has three successful payments and one disputed payment, also resulting in a dispute rate of 0.3333. Failed payments are excluded from the denominator.
DROP TABLE IF EXISTS payment_disputes; DROP TABLE IF EXISTS card_payments; CREATE TABLE card_payments ( payment_id INT, merchant_id INT, payment_status VARCHAR(20), created_at TIMESTAMP, amount DECIMAL(10,2) ); CREATE TABLE payment_disputes ( dispute_id INT, payment_id INT, disputed_at TIMESTAMP, reason VARCHAR(40) ); INSERT INTO card_payments VALUES (1,101,'succeeded','2025-01-03 10:00:00',100.00), (2,101,'succeeded','2025-01-10 11:00:00',50.00), (3,101,'succeeded','2025-01-20 12:00:00',75.00), (4,101,'failed','2025-01-21 12:00:00',25.00), (5,202,'succeeded','2025-01-05 09:00:00',200.00), (6,202,'succeeded','2025-01-15 09:00:00',150.00), (7,202,'succeeded','2025-01-25 09:00:00',125.00); INSERT INTO payment_disputes VALUES (11,1,'2025-01-15 10:00:00','fraud'), (12,5,'2025-01-20 09:00:00','duplicate');