High-Value UPI Transfers for Compliance Review — SQL Problem
The compliance desk reviews every UPI transfer of ₹50,000 or more that actually went through.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #48
Problem statement
The compliance desk reviews every UPI transfer of ₹50,000 or more that actually went through. Failed and pending transfers moved no money and are not reviewed.
Return txn_id, payer_vpa and amount for every transfer with status SUCCESS and an amount of at least 50,000 (a transfer of exactly 50,000 is included). Order the rows by amount descending, then by txn_id ascending.
Tables
Table: UpiTransaction
| Column | Type |
|---|---|
| txn_id | int |
| payer_vpa | varchar |
| payee_vpa | varchar |
| amount | decimal |
| status | enum(SUCCESS, FAILED, PENDING) |
| txn_time | datetime |
Primary key: txn_id.
One row per UPI transfer attempt; amount is in rupees.
Examples
Example 1
UpiTransaction
| txn_id | payer_vpa | payee_vpa | amount | status | txn_time |
|---|---|---|---|---|---|
| 1 | aarav1@okaxis | kirana7@ybl | 1250 | SUCCESS | 2024-08-01 09:12:44 |
| 2 | diya2@oksbi | rent.owner@okicici | 50000 | SUCCESS | 2024-08-01 10:05:10 |
| 3 | rohan3@paytm | car.dealer@okhdfcbank | 175000 | FAILED | 2024-08-02 14:20:00 |
| 4 | meera4@ybl | jewels.shop@ibl | 82500.5 | SUCCESS | 2024-08-03 18:45:31 |
| 5 | kabir5@okaxis | college.fees@oksbi | 49999.99 | SUCCESS | 2024-08-04 11:00:02 |
| 6 | priya6@okicici | builder@axl | 82500.5 | SUCCESS | 2024-08-05 16:30:00 |
| 7 | neha7@oksbi | laptop.store@ybl | 64000 | PENDING | 2024-08-06 20:10:15 |
Output
| txn_id | payer_vpa | amount |
|---|---|---|
| 4 | meera4@ybl | 82500.5 |
| 6 | priya6@okicici | 82500.5 |
| 2 | diya2@oksbi | 50000 |
How to solve High-Value UPI Transfers for Compliance Review
This is a filter and a sort. The WHERE clause keeps a row only when both conditions are true: status = 'SUCCESS' (failed and pending attempts moved no money) and amount >= 50000. The threshold is inclusive, so a transfer of exactly ₹50,000 is in the answer while one of ₹49,999.99 is not — the classic off-by-one of > versus >=.
The statement fixes the order: largest amount first, and among equal amounts the smaller txn_id first. Without the second key two transfers of the same amount could come back in either order, and an ordered comparison would fail depending on how the rows happen to be stored.
An index on (status, amount) lets the database read only the qualifying rows; without one it is a single scan of the table followed by a sort of the matches. Writing the condition as NOT amount < 50000 or status IN ('SUCCESS') is equivalent.
Reference solution (MySQL)
SELECT txn_id, payer_vpa, amount
FROM UpiTransaction
WHERE status = 'SUCCESS' AND amount >= 50000
ORDER BY amount DESC, txn_idAnother way
SELECT txn_id, payer_vpa, amount FROM UpiTransaction WHERE status IN ('SUCCESS') AND NOT amount < 50000 ORDER BY amount DESC, txn_id ASC← Share of Customers Whose First Grocery Order Came Same Day · Customers Without a Verified KYC Document →