PhonePe: QR Stickers That Never Took a Successful Payment
Field sales agents put PhonePe QR stickers up at shops. The merchant team wants a revisit list: stickers that have never received a successful payment,…
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #874
Problem statement
Field sales agents put PhonePe QR stickers up at shops. The merchant team wants a revisit list: stickers that have never received a successful payment, because the shop may have taken the sticker down or never told customers about it.
Return sticker_id and merchant_name for every such sticker. Failed and pending payments do not count as a payment taken, so a sticker whose only scans failed is on the list. Return the rows in any order.
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: QrSticker
| Column | Type |
|---|---|
| sticker_id | int |
| merchant_name | varchar |
| city | varchar |
| installed_on | date |
Primary key: sticker_id.
One row per QR sticker put up at a shop.
Table: QrPayment
| Column | Type |
|---|---|
| payment_id | int |
| sticker_id | int |
| amount | int |
| status | enum(success, failed, pending) |
Primary key: payment_id.
One row per payment made by scanning a sticker; sticker_id always names a sticker.
Examples
Example 1
QrSticker
| sticker_id | merchant_name | city | installed_on |
|---|---|---|---|
| 501 | Sharma Kirana | Pune | 2025-01-04 |
| 502 | Raju Tea Stall | Pune | 2025-01-06 |
| 503 | City Medicals | Nagpur | 2025-01-09 |
| 504 | Lakshmi Sweets | Nashik | 2025-01-11 |
| 505 | Om Sai Traders | Pune | 2025-01-15 |
QrPayment
| payment_id | sticker_id | amount | status |
|---|---|---|---|
| 1 | 501 | 240 | success |
| 2 | 501 | 60 | failed |
| 3 | 503 | 410 | failed |
| 4 | 503 | 410 | pending |
| 5 | 505 | 35 | success |
| 6 | 505 | 90 | success |
Output
| sticker_id | merchant_name |
|---|---|
| 502 | Raju Tea Stall |
| 503 | City Medicals |
| 504 | Lakshmi Sweets |
How to solve PhonePe: QR Stickers That Never Took a Successful Payment
The question is about something that did not happen, which is an anti join: keep each sticker for which no successful payment exists. The cleanest way to say that is NOT EXISTS with a correlated subquery that looks for a payment on the same sticker with status = 'success'.
The LEFT JOIN form needs care. The condition p.status = 'success' must sit in the ON clause, so a sticker whose payments all failed joins to nothing and comes out with NULLs, which WHERE p.payment_id IS NULL keeps. Put the status test in WHERE instead and that sticker's failed rows would join, then be filtered away, and the sticker would vanish from the answer — the classic outer-join mistake.
NOT IN works here as well because QrPayment.sticker_id is never NULL; if it could be, a single NULL in the subquery would make NOT IN return nothing. All three read each table once with an index on QrPayment(sticker_id, status).
Reference solution (MySQL)
SELECT s.sticker_id, s.merchant_name
FROM QrSticker s
LEFT JOIN QrPayment p ON p.sticker_id = s.sticker_id AND p.status = 'success'
WHERE p.payment_id IS NULLAnother way
SELECT sticker_id, merchant_name FROM QrSticker s WHERE NOT EXISTS (SELECT 1 FROM QrPayment p WHERE p.sticker_id = s.sticker_id AND p.status = 'success')Another way
SELECT sticker_id, merchant_name FROM QrSticker WHERE sticker_id NOT IN (SELECT sticker_id FROM QrPayment WHERE status = 'success')← PhonePe: UPI Lite Payments Above the Per-Payment Cap · PhonePe: Users With Three or More Active Linked Bank Accounts →