PhonePe: UPI Lite Payments Above the Per-Payment Cap
UPI Lite pays small amounts from an on-device balance without a UPI PIN, so the risk team caps a single Lite payment at ₹500.
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #873
Problem statement
UPI Lite pays small amounts from an on-device balance without a UPI PIN, so the risk team caps a single Lite payment at ₹500. After a release, they want to see whether any payment slipped past the cap.
Return every successful Lite payment whose amount is strictly greater than 500, with the columns payment_id, user_id and amount. A payment of exactly 500 is within the cap, and failed payments moved no money. Order the rows by amount from highest to lowest, then by payment_id ascending.
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: LitePayment
| Column | Type |
|---|---|
| payment_id | int |
| user_id | int |
| amount | int |
| paid_at | datetime |
| status | enum(success, failed) |
Primary key: payment_id.
One row per UPI Lite payment attempt; amount is in whole rupees.
Examples
Example 1
LitePayment
| payment_id | user_id | amount | paid_at | status |
|---|---|---|---|---|
| 1 | 101 | 500 | 2025-02-03 09:12:40 | success |
| 2 | 102 | 820 | 2025-02-03 10:05:11 | success |
| 3 | 101 | 750 | 2025-02-04 18:30:02 | failed |
| 4 | 103 | 501 | 2025-02-05 08:00:59 | success |
| 5 | 104 | 820 | 2025-02-05 13:44:20 | success |
| 6 | 102 | 120 | 2025-02-06 21:10:00 | success |
| 7 | 105 | 1000 | 2025-02-07 07:15:33 | success |
Output
| payment_id | user_id | amount |
|---|---|---|
| 7 | 105 | 1000 |
| 2 | 102 | 820 |
| 5 | 104 | 820 |
| 4 | 103 | 501 |
How to solve PhonePe: UPI Lite Payments Above the Per-Payment Cap
This is a filter and a sort on one table. A payment is a breach of the cap only if it actually went through, so status = 'success' is the first condition; a failed attempt above ₹500 moved no money and is not a breach. The second is amount > 500 — strict, because a payment of exactly the cap is allowed.
The order is fixed by the statement, so the query needs a full ORDER BY: amount DESC puts the biggest breaches first, and payment_id breaks a tie between two payments of the same amount, which is what makes the result the same however the rows are stored.
The alternatives write the same conditions differently — NOT amount <= 500, amount >= 501 for whole-rupee amounts, and status <> 'failed', which is the same test while there are only two statuses. One pass over the table; an index on (status, amount) would read only the breaches.
Reference solution (MySQL)
SELECT payment_id, user_id, amount
FROM LitePayment
WHERE status = 'success'
AND amount > 500
ORDER BY amount DESC, payment_idAnother way
SELECT payment_id, user_id, amount FROM LitePayment WHERE status IN ('success') AND NOT amount <= 500 ORDER BY amount DESC, payment_id ASCAnother way
SELECT payment_id, user_id, amount FROM LitePayment WHERE amount >= 501 AND status <> 'failed' ORDER BY 3 DESC, 1← Paytm: Return Flights Booked Within Ten Days · PhonePe: QR Stickers That Never Took a Successful Payment →