Stripe's dispute operations team needs to measure whether accounts submit complete evidence before response deadlines.
Analyze disputes with opened_at from 2025-01-01 through 2025-03-31, inclusive, where dispute_status is needs_response. A dispute is evidence-ready only when it has at least one proof_of_delivery submission and at least one customer_communication submission. Each required submission must have a non-NULL submitted_at date on or before that dispute's response_due_at; compare the calendar-date portion of submitted_at to response_due_at. A repeated submission of the same evidence_type still counts only once for that dispute.
opened_at
2025-01-01
2025-03-31
dispute_status
needs_response
proof_of_delivery
customer_communication
NULL
submitted_at
response_due_at
evidence_type
For each account_name with at least 2 eligible disputes, calculate readiness_rate as (ready_disputes / eligible_disputes) * 100. Return account_name, eligible_disputes, ready_disputes, and readiness_rate, in that order. Round readiness_rate to exactly 2 decimal places. Sort by readiness_rate descending, then account_name ascending.
account_name
2
readiness_rate
(ready_disputes / eligible_disputes) * 100
eligible_disputes
ready_disputes
disputes
dispute_id
INTEGER
VARCHAR(120)
DATE
VARCHAR(30)
evidence_submissions
submission_id
VARCHAR(50)
TIMESTAMP
101
Vercel
2025-01-10
2025-01-24
102
2025-02-05
2025-02-19
103
Linear
2025-01-12
2025-01-26
104
2025-02-16
2025-03-02
105
2025-03-01
2025-03-15
won
106
Supabase
2025-03-05
2025-03-19
1
2025-01-15 09:30:00
2025-01-20 14:10:00
3
2025-01-21 08:00:00
4
2025-02-12 10:00:00
5
6
2025-01-18 12:00:00
7
2025-01-27 09:00:00
8
2025-02-25 15:00:00
9
2025-03-02 18:00:00
Vercel has 2 eligible disputes, and only dispute 101 has both required evidence types submitted by its deadline, so its readiness rate is 50.00. Linear also has 2 eligible disputes, and only dispute 104 is evidence-ready; its customer communication submission is valid because it occurs on 2025-03-02, the response deadline. Supabase is excluded because it has fewer than 2 eligible disputes. Since Linear and Vercel tie at 50.00, Linear appears first alphabetically.
50.00
DROP TABLE IF EXISTS evidence_submissions; DROP TABLE IF EXISTS disputes; CREATE TABLE disputes ( dispute_id INTEGER, account_name VARCHAR(120), opened_at DATE, response_due_at DATE, dispute_status VARCHAR(30) ); CREATE TABLE evidence_submissions ( submission_id INTEGER, dispute_id INTEGER, evidence_type VARCHAR(50), submitted_at TIMESTAMP ); INSERT INTO disputes VALUES (101, 'Vercel', '2025-01-10', '2025-01-24', 'needs_response'), (102, 'Vercel', '2025-02-05', '2025-02-19', 'needs_response'), (103, 'Linear', '2025-01-12', '2025-01-26', 'needs_response'), (104, 'Linear', '2025-02-16', '2025-03-02', 'needs_response'), (105, 'Linear', '2025-03-01', '2025-03-15', 'won'), (106, 'Supabase', '2025-03-05', '2025-03-19', 'needs_response'); INSERT INTO evidence_submissions VALUES (1, 101, 'proof_of_delivery', '2025-01-15 09:30:00'), (2, 101, 'customer_communication', '2025-01-20 14:10:00'), (3, 101, 'proof_of_delivery', '2025-01-21 08:00:00'), (4, 102, 'proof_of_delivery', '2025-02-12 10:00:00'), (5, 102, 'customer_communication', NULL), (6, 103, 'proof_of_delivery', '2025-01-18 12:00:00'), (7, 103, 'customer_communication', '2025-01-27 09:00:00'), (8, 104, 'proof_of_delivery', '2025-02-25 15:00:00'), (9, 104, 'customer_communication', '2025-03-02 18:00:00');