PhonePe: UPI Lite Payments Above the Per-Payment Cap

Easy
873.

PhonePe: UPI Lite Payments Above the Per-Payment Cap

EasyBasics

MySQL

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

ColumnTypeValues
payment_idint
user_idint
amountint
paid_atdatetime
statusenumsuccess, failed

Primary key: payment_id.

One row per UPI Lite payment attempt; amount is in whole rupees.

Example

The rows must come back in the order shown.

LitePayment

payment_iduser_idamountpaid_atstatus
11015002025-02-03 09:12:40success
21028202025-02-03 10:05:11success
31017502025-02-04 18:30:02failed
41035012025-02-05 08:00:59success
51048202025-02-05 13:44:20success
61021202025-02-06 21:10:00success
710510002025-02-07 07:15:33success

Output

payment_iduser_idamount
71051000
2102820
5104820
4103501
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.

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

ColumnType
payment_idint
user_idint
amountint
paid_atdatetime
statusenum(success, failed)

Primary key: payment_id.

One row per UPI Lite payment attempt; amount is in whole rupees.

Examples

Example 1

LitePayment

payment_iduser_idamountpaid_atstatus
11015002025-02-03 09:12:40success
21028202025-02-03 10:05:11success
31017502025-02-04 18:30:02failed
41035012025-02-05 08:00:59success
51048202025-02-05 13:44:20success
61021202025-02-06 21:10:00success
710510002025-02-07 07:15:33success

Output

payment_iduser_idamount
71051000
2102820
5104820
4103501

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_id

Another way

SELECT payment_id, user_id, amount FROM LitePayment WHERE status IN ('success') AND NOT amount <= 500 ORDER BY amount DESC, payment_id ASC

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