Razorpay: Card Payment Success Rate by Issuing Bank

Medium
906.

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.

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

ColumnTypeValues
payment_idint
issuer_bankvarchar
card_networkvarchar
card_typeenumcredit, debit
statusenumcaptured, authorized, failed

Primary key: payment_id.

One row per card payment attempt; a shopper retrying is a new row.

Example

The rows must come back in the order shown.

CardAttempt

payment_idissuer_bankcard_networkcard_typestatus
1HDFC BankVisacreditcaptured
2HDFC BankVisadebitfailed
3HDFC BankMastercardcreditcaptured
4HDFC BankRuPaydebitauthorized
5HDFC BankVisacreditcaptured
6SBIRuPaydebitfailed
7SBIRuPaydebitcaptured
8SBIVisadebitfailed
9SBIRuPaycreditcaptured
10SBIMastercarddebitfailed
2 more rows

Output

issuer_bankattemptssuccess_rate
HDFC Bank580
SBI540
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
payment_idint
issuer_bankvarchar
card_networkvarchar
card_typeenum(credit, debit)
statusenum(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_idissuer_bankcard_networkcard_typestatus
1HDFC BankVisacreditcaptured
2HDFC BankVisadebitfailed
3HDFC BankMastercardcreditcaptured
4HDFC BankRuPaydebitauthorized
5HDFC BankVisacreditcaptured
6SBIRuPaydebitfailed
7SBIRuPaydebitcaptured
8SBIVisadebitfailed
9SBIRuPaycreditcaptured
10SBIMastercarddebitfailed
11Axis BankVisacreditcaptured
12NULLVisacreditfailed

Output

issuer_bankattemptssuccess_rate
HDFC Bank580
SBI540

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_bank

Another 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 →