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
| Column | Type |
|---|---|
| txn_id | int |
| payee_vpa | varchar |
| amount | int |
| status | enum(SUCCESS, FAILED) |
| txn_date | date |
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_id | payee_vpa | amount | status | txn_date |
|---|---|---|---|---|
| 1 | kirana7@ybl | 5000 | SUCCESS | 2024-07-02 |
| 2 | chaiwala@oksbi | 3000 | SUCCESS | 2024-07-05 |
| 3 | pharma.plus@okaxis | 3000 | SUCCESS | 2024-07-09 |
| 4 | tailor.raj@paytm | 1000 | SUCCESS | 2024-07-15 |
| 5 | kirana7@ybl | 9000 | FAILED | 2024-07-20 |
| 6 | bookstore@paytm | 6000 | SUCCESS | 2024-08-01 |
| 7 | kirana7@ybl | 2500 | SUCCESS | 2024-08-03 |
| 8 | dhaba22@ybl | 2500 | SUCCESS | 2024-08-10 |
| 9 | chaiwala@oksbi | 4000 | SUCCESS | 2024-08-31 |
Output
| month | psp_handle | total_amount | handle_rank |
|---|---|---|---|
| 2024-07 | ybl | 5000 | 1 |
| 2024-07 | okaxis | 3000 | 2 |
| 2024-07 | oksbi | 3000 | 2 |
| 2024-08 | paytm | 6000 | 1 |
| 2024-08 | ybl | 5000 | 2 |
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_handleAnother 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 →