PhonePe: Most Frequent UPI Decline Code at Each Remitter Bank

Medium
881.

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. 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

ColumnTypeValues
txn_idint
remitter_bankvarchar
statusenumsuccess, failed
error_codevarchar
txn_atdatetime

Primary key: txn_id.

One row per UPI transaction; error_code is NULL for a success and set for every failure.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

UpiTxn

txn_idremitter_bankstatuserror_codetxn_at
1SBIfailedU302025-06-02 10:01:00
2SBIfailedZ92025-06-02 10:04:00
3SBIfailedU302025-06-02 11:20:00
4SBIsuccessNULL2025-06-02 11:25:00
5HDFCfailedZM2025-06-02 12:00:00
6HDFCfailedXH2025-06-02 12:30:00
7HDFCsuccessNULL2025-06-02 13:10:00
8ICICIsuccessNULL2025-06-02 13:15:00
9SBIfailedZ92025-06-02 14:00:00
10SBIfailedU302025-06-02 15:45:00

Output

remitter_bankerror_codefailures
HDFCXH1
HDFCZM1
SBIU303
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.

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

ColumnType
txn_idint
remitter_bankvarchar
statusenum(success, failed)
error_codevarchar
txn_atdatetime

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_idremitter_bankstatuserror_codetxn_at
1SBIfailedU302025-06-02 10:01:00
2SBIfailedZ92025-06-02 10:04:00
3SBIfailedU302025-06-02 11:20:00
4SBIsuccessNULL2025-06-02 11:25:00
5HDFCfailedZM2025-06-02 12:00:00
6HDFCfailedXH2025-06-02 12:30:00
7HDFCsuccessNULL2025-06-02 13:10:00
8ICICIsuccessNULL2025-06-02 13:15:00
9SBIfailedZ92025-06-02 14:00:00
10SBIfailedU302025-06-02 15:45:00

Output

remitter_bankerror_codefailures
HDFCXH1
HDFCZM1
SBIU303

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 = 1

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