High-Value UPI Transfers for Compliance Review

Easy
48.

High-Value UPI Transfers for Compliance Review

EasyBasics

MySQL

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

ColumnTypeValues
txn_idint
payer_vpavarchar
payee_vpavarchar
amountdecimal
statusenumSUCCESS, FAILED, PENDING
txn_timedatetime

Primary key: txn_id.

One row per UPI transfer attempt; amount is in rupees.

Example

The rows must come back in the order shown.

UpiTransaction

txn_idpayer_vpapayee_vpaamountstatustxn_time
1aarav1@okaxiskirana7@ybl1250SUCCESS2024-08-01 09:12:44
2diya2@oksbirent.owner@okicici50000SUCCESS2024-08-01 10:05:10
3rohan3@paytmcar.dealer@okhdfcbank175000FAILED2024-08-02 14:20:00
4meera4@ybljewels.shop@ibl82500.5SUCCESS2024-08-03 18:45:31
5kabir5@okaxiscollege.fees@oksbi49999.99SUCCESS2024-08-04 11:00:02
6priya6@okicicibuilder@axl82500.5SUCCESS2024-08-05 16:30:00
7neha7@oksbilaptop.store@ybl64000PENDING2024-08-06 20:10:15

Output

txn_idpayer_vpaamount
4meera4@ybl82500.5
6priya6@okicici82500.5
2diya2@oksbi50000
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.

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

ColumnType
txn_idint
payer_vpavarchar
payee_vpavarchar
amountdecimal
statusenum(SUCCESS, FAILED, PENDING)
txn_timedatetime

Primary key: txn_id.

One row per UPI transfer attempt; amount is in rupees.

Examples

Example 1

UpiTransaction

txn_idpayer_vpapayee_vpaamountstatustxn_time
1aarav1@okaxiskirana7@ybl1250SUCCESS2024-08-01 09:12:44
2diya2@oksbirent.owner@okicici50000SUCCESS2024-08-01 10:05:10
3rohan3@paytmcar.dealer@okhdfcbank175000FAILED2024-08-02 14:20:00
4meera4@ybljewels.shop@ibl82500.5SUCCESS2024-08-03 18:45:31
5kabir5@okaxiscollege.fees@oksbi49999.99SUCCESS2024-08-04 11:00:02
6priya6@okicicibuilder@axl82500.5SUCCESS2024-08-05 16:30:00
7neha7@oksbilaptop.store@ybl64000PENDING2024-08-06 20:10:15

Output

txn_idpayer_vpaamount
4meera4@ybl82500.5
6priya6@okicici82500.5
2diya2@oksbi50000

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_id

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