PhonePe: QR Stickers That Never Took a Successful Payment

Easy
874.

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, 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

ColumnType
sticker_idint
merchant_namevarchar
cityvarchar
installed_ondate

Primary key: sticker_id.

One row per QR sticker put up at a shop.

Table: QrPayment

ColumnTypeValues
payment_idint
sticker_idint
amountint
statusenumsuccess, failed, pending

Primary key: payment_id.

One row per payment made by scanning a sticker; sticker_id always names a sticker.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

QrSticker

sticker_idmerchant_namecityinstalled_on
501Sharma KiranaPune2025-01-04
502Raju Tea StallPune2025-01-06
503City MedicalsNagpur2025-01-09
504Lakshmi SweetsNashik2025-01-11
505Om Sai TradersPune2025-01-15

QrPayment

payment_idsticker_idamountstatus
1501240success
250160failed
3503410failed
4503410pending
550535success
650590success

Output

sticker_idmerchant_name
502Raju Tea Stall
503City Medicals
504Lakshmi Sweets
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: 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

ColumnType
sticker_idint
merchant_namevarchar
cityvarchar
installed_ondate

Primary key: sticker_id.

One row per QR sticker put up at a shop.

Table: QrPayment

ColumnType
payment_idint
sticker_idint
amountint
statusenum(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_idmerchant_namecityinstalled_on
501Sharma KiranaPune2025-01-04
502Raju Tea StallPune2025-01-06
503City MedicalsNagpur2025-01-09
504Lakshmi SweetsNashik2025-01-11
505Om Sai TradersPune2025-01-15

QrPayment

payment_idsticker_idamountstatus
1501240success
250160failed
3503410failed
4503410pending
550535success
650590success

Output

sticker_idmerchant_name
502Raju Tea Stall
503City Medicals
504Lakshmi 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 NULL

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