PhonePe: Most Frequent UPI Decline Code at Each Remitter Bank
When a UPI payment fails, the remitter bank (the payer's bank) answers with a decline code.
- Difficulty: Medium
- Topics: Aggregation, Window Functions
- Dialect: MySQL
- Problem: #881
Problem statement
When a UPI payment fails, the remitter bank (the payer's bank) answers with a decline code. The bank-partnerships team takes each bank's single most common code to its weekly call with that bank.
For every bank with at least one failed transaction, return remitter_bank, error_code and failures, the number of failed transactions with that code. Keep only the code with the most failures at that bank; if several codes tie for the most, return all of them. Successful transactions have no code and do not count. Return the rows in any order.
A practice scenario set at PhonePe: the tables and data are invented, and this is not a question from, or endorsed by, PhonePe.
Tables
Table: UpiTxn
| Column | Type |
|---|---|
| txn_id | int |
| remitter_bank | varchar |
| status | enum(success, failed) |
| error_code | varchar |
| txn_at | datetime |
Primary key: txn_id.
One row per UPI transaction; error_code is NULL for a success and set for every failure.
Examples
Example 1
UpiTxn
| txn_id | remitter_bank | status | error_code | txn_at |
|---|---|---|---|---|
| 1 | SBI | failed | U30 | 2025-06-02 10:01:00 |
| 2 | SBI | failed | Z9 | 2025-06-02 10:04:00 |
| 3 | SBI | failed | U30 | 2025-06-02 11:20:00 |
| 4 | SBI | success | NULL | 2025-06-02 11:25:00 |
| 5 | HDFC | failed | ZM | 2025-06-02 12:00:00 |
| 6 | HDFC | failed | XH | 2025-06-02 12:30:00 |
| 7 | HDFC | success | NULL | 2025-06-02 13:10:00 |
| 8 | ICICI | success | NULL | 2025-06-02 13:15:00 |
| 9 | SBI | failed | Z9 | 2025-06-02 14:00:00 |
| 10 | SBI | failed | U30 | 2025-06-02 15:45:00 |
Output
| remitter_bank | error_code | failures |
|---|---|---|
| HDFC | XH | 1 |
| HDFC | ZM | 1 |
| SBI | U30 | 3 |
How to solve PhonePe: Most Frequent UPI Decline Code at Each Remitter Bank
The work happens in two levels. First, aggregate the failed transactions to one row per (remitter_bank, error_code) with its count — successes are dropped in WHERE, and since every failure carries a code there is no NULL group. Second, within each bank, keep the rows whose count is the bank's maximum.
RANK() over PARTITION BY remitter_bank ORDER BY failures DESC gives position 1 to every code that shares the top count, which is exactly the tie rule; ROW_NUMBER() would keep one tied code arbitrarily and make the answer depend on storage order. A bank with only successful transactions has no rows in the counts and does not appear.
The alternatives avoid window functions: NOT EXISTS keeps a code when no code at the same bank has a larger count, and the join version compares each count with the per-bank MAX. All three aggregate once; the comparison step works on the small table of counts, not the raw transactions.
Reference solution (MySQL)
WITH counts AS (
SELECT remitter_bank, error_code, COUNT(*) AS failures
FROM UpiTxn
WHERE status = 'failed'
GROUP BY remitter_bank, error_code
), ranked AS (
SELECT remitter_bank, error_code, failures,
RANK() OVER (PARTITION BY remitter_bank ORDER BY failures DESC) AS pos
FROM counts
)
SELECT remitter_bank, error_code, failures
FROM ranked
WHERE pos = 1Another way
WITH counts AS (SELECT remitter_bank, error_code, COUNT(*) AS failures FROM UpiTxn WHERE status = 'failed' GROUP BY remitter_bank, error_code)
SELECT c.remitter_bank, c.error_code, c.failures FROM counts c
WHERE NOT EXISTS (SELECT 1 FROM counts d WHERE d.remitter_bank = c.remitter_bank AND d.failures > c.failures)Another way
WITH counts AS (SELECT remitter_bank, error_code, COUNT(*) AS failures FROM UpiTxn WHERE status = 'failed' GROUP BY remitter_bank, error_code)
SELECT c.remitter_bank, c.error_code, c.failures FROM counts c
JOIN (SELECT remitter_bank, MAX(failures) AS top FROM counts GROUP BY remitter_bank) m ON m.remitter_bank = c.remitter_bank AND m.top = c.failures← PhonePe: Labelling Scratch Card Rewards for the Rewards Page · PhonePe: Monthly P2P Versus P2M Payment Value in 2024 →