Top Two PSP Handles by Monthly UPI Volume

Hard
71.

Top Two PSP Handles by Monthly UPI Volume

NPCI watches how concentrated UPI is among payment-service providers. The provider of a payment is the handle after the @ in the payee's VPA (kirana7@ybl → ybl). For every month, rank the handles by their total amount of successful payments and keep the top two totals — handles with equal totals share a rank, so a month can list more than two handles, and the next distinct total after a tie is still rank 2.

Return month ('YYYY-MM'), psp_handle, total_amount and handle_rank (1 or 2). Order the rows by month, then handle_rank, then psp_handle.

Tables

Table: UpiCollection

ColumnTypeValues
txn_idint
payee_vpavarchar
amountint
statusenumSUCCESS, FAILED
txn_datedate

Primary key: txn_id.

One row per payment received by a merchant VPA; VPAs are lower case and contain exactly one @.

Example

The rows must come back in the order shown.

UpiCollection

txn_idpayee_vpaamountstatustxn_date
1kirana7@ybl5000SUCCESS2024-07-02
2chaiwala@oksbi3000SUCCESS2024-07-05
3pharma.plus@okaxis3000SUCCESS2024-07-09
4tailor.raj@paytm1000SUCCESS2024-07-15
5kirana7@ybl9000FAILED2024-07-20
6bookstore@paytm6000SUCCESS2024-08-01
7kirana7@ybl2500SUCCESS2024-08-03
8dhaba22@ybl2500SUCCESS2024-08-10
9chaiwala@oksbi4000SUCCESS2024-08-31

Output

monthpsp_handletotal_amounthandle_rank
2024-07ybl50001
2024-07okaxis30002
2024-07oksbi30002
2024-08paytm60001
2024-08ybl50002
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.

Top Two PSP Handles by Monthly UPI Volume — SQL Problem

NPCI watches how concentrated UPI is among payment-service providers.

  • Difficulty: Hard
  • Topics: Strings, Window Functions, Dates
  • Dialect: MySQL
  • Problem: #71

Problem statement

NPCI watches how concentrated UPI is among payment-service providers. The provider of a payment is the handle after the @ in the payee's VPA (kirana7@ybl → ybl). For every month, rank the handles by their total amount of successful payments and keep the top two totals — handles with equal totals share a rank, so a month can list more than two handles, and the next distinct total after a tie is still rank 2.

Return month ('YYYY-MM'), psp_handle, total_amount and handle_rank (1 or 2). Order the rows by month, then handle_rank, then psp_handle.

Tables

Table: UpiCollection

ColumnType
txn_idint
payee_vpavarchar
amountint
statusenum(SUCCESS, FAILED)
txn_datedate

Primary key: txn_id.

One row per payment received by a merchant VPA; VPAs are lower case and contain exactly one @.

Examples

Example 1

UpiCollection

txn_idpayee_vpaamountstatustxn_date
1kirana7@ybl5000SUCCESS2024-07-02
2chaiwala@oksbi3000SUCCESS2024-07-05
3pharma.plus@okaxis3000SUCCESS2024-07-09
4tailor.raj@paytm1000SUCCESS2024-07-15
5kirana7@ybl9000FAILED2024-07-20
6bookstore@paytm6000SUCCESS2024-08-01
7kirana7@ybl2500SUCCESS2024-08-03
8dhaba22@ybl2500SUCCESS2024-08-10
9chaiwala@oksbi4000SUCCESS2024-08-31

Output

monthpsp_handletotal_amounthandle_rank
2024-07ybl50001
2024-07okaxis30002
2024-07oksbi30002
2024-08paytm60001
2024-08ybl50002

How to solve Top Two PSP Handles by Monthly UPI Volume

Three steps in a CTE chain. Parse: SUBSTRING_INDEX(payee_vpa, '@', -1) returns everything after the last @, the PSP handle (SUBSTRING(payee_vpa, LOCATE('@', payee_vpa) + 1) is the equivalent). Aggregate: filter to successful payments and group by the month label and the handle, summing amounts — two merchants on @ybl add up to one handle total. Rank: within each month, DENSE_RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) and keep ranks 1 and 2.

The tie rule decides the ranking function. With totals 5,000, 3,000, 3,000, 1,000, DENSE_RANK gives 1, 2, 2, 3, so the two handles at 3,000 both make the list and the 1,000 does not. RANK gives 1, 2, 2, 4 — the same answer here but not after a tie at the top (6,000, 6,000, 4,000 → 1, 1, 3 drops the 4,000, which the statement keeps as rank 2). ROW_NUMBER would drop a tied handle arbitrarily.

The window-free version computes the same dense rank as one plus the number of distinct larger totals in the month, with a correlated COUNT(DISTINCT …). Ranking runs on the small aggregated table, so either form is cheap once the scan and group-by are done.

Reference solution (MySQL)

WITH totals AS (
  SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month,
         SUBSTRING_INDEX(payee_vpa, '@', -1) AS psp_handle,
         SUM(amount) AS total_amount
  FROM UpiCollection
  WHERE status = 'SUCCESS'
  GROUP BY DATE_FORMAT(txn_date, '%Y-%m'), SUBSTRING_INDEX(payee_vpa, '@', -1)
), ranked AS (
  SELECT month, psp_handle, total_amount,
         DENSE_RANK() OVER (PARTITION BY month ORDER BY total_amount DESC) AS handle_rank
  FROM totals
)
SELECT month, psp_handle, total_amount, handle_rank
FROM ranked
WHERE handle_rank <= 2
ORDER BY month, handle_rank, psp_handle

Another way

WITH totals AS (
  SELECT LEFT(txn_date, 7) AS month, SUBSTRING(payee_vpa, LOCATE('@', payee_vpa) + 1) AS psp_handle, SUM(amount) AS total_amount
  FROM UpiCollection WHERE status = 'SUCCESS'
  GROUP BY LEFT(txn_date, 7), SUBSTRING(payee_vpa, LOCATE('@', payee_vpa) + 1)
)
SELECT * FROM (
  SELECT t.month, t.psp_handle, t.total_amount,
    (SELECT COUNT(DISTINCT u.total_amount) FROM totals u WHERE u.month = t.month AND u.total_amount > t.total_amount) + 1 AS handle_rank
  FROM totals t
) r WHERE handle_rank <= 2 ORDER BY month, handle_rank, psp_handle

← UPI App New-User Retention in the Following Month · Days From Account Opening to a ₹1 Lakh Balance →