Razorpay: Card Payment Success Rate by Issuing Bank
Razorpay's payments performance team tracks which card issuers decline the most, to decide where to route and whom to escalate to.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #906
Problem statement
Razorpay's payments performance team tracks which card issuers decline the most, to decide where to route and whom to escalate to.
A card attempt is successful when its status is captured or authorized (the issuer approved it). Ignore attempts whose issuer_bank is NULL (the BIN lookup failed). For every issuing bank with at least 5 attempts, return issuer_bank, attempts and success_rate = 100 × successful / attempts, rounded to 2 decimals. Order by success_rate descending, then issuer_bank ascending.
A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.
Tables
Table: CardAttempt
| Column | Type |
|---|---|
| payment_id | int |
| issuer_bank | varchar |
| card_network | varchar |
| card_type | enum(credit, debit) |
| status | enum(captured, authorized, failed) |
Primary key: payment_id.
One row per card payment attempt; a shopper retrying is a new row.
Examples
Example 1
CardAttempt
| payment_id | issuer_bank | card_network | card_type | status |
|---|---|---|---|---|
| 1 | HDFC Bank | Visa | credit | captured |
| 2 | HDFC Bank | Visa | debit | failed |
| 3 | HDFC Bank | Mastercard | credit | captured |
| 4 | HDFC Bank | RuPay | debit | authorized |
| 5 | HDFC Bank | Visa | credit | captured |
| 6 | SBI | RuPay | debit | failed |
| 7 | SBI | RuPay | debit | captured |
| 8 | SBI | Visa | debit | failed |
| 9 | SBI | RuPay | credit | captured |
| 10 | SBI | Mastercard | debit | failed |
| 11 | Axis Bank | Visa | credit | captured |
| 12 | NULL | Visa | credit | failed |
Output
| issuer_bank | attempts | success_rate |
|---|---|---|
| HDFC Bank | 5 | 80 |
| SBI | 5 | 40 |
How to solve Razorpay: Card Payment Success Rate by Issuing Bank
Group the attempts by issuer_bank and compute two numbers per group: COUNT(*) for attempts and a conditional sum for the approvals, where both captured and authorized count (authorized money has been approved by the issuer even if the merchant has not captured it yet). The rate is 100 × approvals / attempts, rounded at the end.
GROUP BY puts all NULLs in one group, so attempts with an unknown issuer would otherwise appear as a NULL bank; WHERE issuer_bank IS NOT NULL removes them before grouping. The minimum volume rule — at least 5 attempts — is a condition on the group and belongs in HAVING; a bank with exactly 5 attempts is kept.
The statement fixes the order, with the bank name breaking ties between equal rates. The alternative counts successes as attempts minus failures (SUM(status = 'failed') uses MySQL's 1/0 booleans) inside a derived table and filters the NULL group and the volume rule outside. Both are a single pass over the attempts.
Reference solution (MySQL)
SELECT issuer_bank,
COUNT(*) AS attempts,
ROUND(100 * SUM(CASE WHEN status IN ('captured', 'authorized') THEN 1 ELSE 0 END) / COUNT(*), 2) AS success_rate
FROM CardAttempt
WHERE issuer_bank IS NOT NULL
GROUP BY issuer_bank
HAVING COUNT(*) >= 5
ORDER BY success_rate DESC, issuer_bankAnother way
SELECT issuer_bank, attempts, ROUND(100 * ok / attempts, 2) AS success_rate
FROM (SELECT issuer_bank, COUNT(*) AS attempts, COUNT(*) - SUM(status = 'failed') AS ok
FROM CardAttempt GROUP BY issuer_bank) t
WHERE issuer_bank IS NOT NULL AND attempts >= 5
ORDER BY success_rate DESC, issuer_bank← Razorpay: Magic Checkout Conversion Rate by Merchant · Razorpay: Chargeback Rate by Merchant Category →