Razorpay: Open QR Codes That Never Received a Payment

Easy
901.

Razorpay: Open QR Codes That Never Received a Payment

Razorpay's offline payments team suspects that some QR codes printed for shop counters were never put up. They want the list of QR codes that are still open and have never received a payment, so field agents can visit those counters.

Return qr_id, merchant_id and location_label for every QR code with is_closed = 0 and no row in QrPayment. Closed QR codes are never reported. Any order.

A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.

Tables

Table: QrCode

ColumnType
qr_idint
merchant_idint
location_labelvarchar
created_ondate
is_closedbool

Primary key: qr_id.

One row per QR code issued to a merchant; location_label is where the merchant said it would be displayed.

Table: QrPayment

ColumnType
payment_idint
qr_idint
amountint
received_atdatetime

Primary key: payment_id.

One row per payment received on a QR code; qr_id always names an existing code.

Example

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

QrCode

qr_idmerchant_idlocation_labelcreated_onis_closed
1201Billing Desk2025-02-010
2201Counter 22025-02-010
3202Food Court Kiosk2025-02-101
4203Delivery Van 32025-02-120
5203Pharmacy Counter2025-02-120

QrPayment

payment_idqr_idamountreceived_at
900112402025-02-03 11:20:00
90021852025-02-03 12:05:00
9003412002025-02-14 18:40:00

Output

qr_idmerchant_idlocation_label
2201Counter 2
5203Pharmacy Counter
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.

Razorpay: Open QR Codes That Never Received a Payment

Razorpay's offline payments team suspects that some QR codes printed for shop counters were never put up.

  • Difficulty: Easy
  • Topics: Joins, Subqueries
  • Dialect: MySQL
  • Problem: #901

Problem statement

Razorpay's offline payments team suspects that some QR codes printed for shop counters were never put up. They want the list of QR codes that are still open and have never received a payment, so field agents can visit those counters.

Return qr_id, merchant_id and location_label for every QR code with is_closed = 0 and no row in QrPayment. Closed QR codes are never reported. Any order.

A practice scenario set at Razorpay: the tables and data are invented, and this is not a question from, or endorsed by, Razorpay.

Tables

Table: QrCode

ColumnType
qr_idint
merchant_idint
location_labelvarchar
created_ondate
is_closedbool

Primary key: qr_id.

One row per QR code issued to a merchant; location_label is where the merchant said it would be displayed.

Table: QrPayment

ColumnType
payment_idint
qr_idint
amountint
received_atdatetime

Primary key: payment_id.

One row per payment received on a QR code; qr_id always names an existing code.

Examples

Example 1

QrCode

qr_idmerchant_idlocation_labelcreated_onis_closed
1201Billing Desk2025-02-010
2201Counter 22025-02-010
3202Food Court Kiosk2025-02-101
4203Delivery Van 32025-02-120
5203Pharmacy Counter2025-02-120

QrPayment

payment_idqr_idamountreceived_at
900112402025-02-03 11:20:00
90021852025-02-03 12:05:00
9003412002025-02-14 18:40:00

Output

qr_idmerchant_idlocation_label
2201Counter 2
5203Pharmacy Counter

How to solve Razorpay: Open QR Codes That Never Received a Payment

"Never received a payment" means there is no matching row in QrPayment, the classic anti join. With a LEFT JOIN, every QR code appears at least once; a code with payments appears once per payment with real values, and a code without payments appears once with all of p's columns NULL. Keeping p.payment_id IS NULL — a primary key, so it is NULL only for a missing partner — leaves exactly the codes nobody paid on.

The status test q.is_closed = 0 belongs in WHERE too: a closed code is not a field visit, whatever its payments.

NOT EXISTS states the same thing directly and stops at the first payment it finds per code. NOT IN works here because QrPayment.qr_id is never NULL — if the subquery could return a NULL, NOT IN would return no rows at all, which is why NOT EXISTS is the safer habit. All three are a single pass with an index on QrPayment(qr_id).

Reference solution (MySQL)

SELECT q.qr_id, q.merchant_id, q.location_label
FROM QrCode q
LEFT JOIN QrPayment p ON p.qr_id = q.qr_id
WHERE q.is_closed = 0 AND p.payment_id IS NULL

Another way

SELECT qr_id, merchant_id, location_label FROM QrCode q WHERE is_closed = 0 AND NOT EXISTS (SELECT 1 FROM QrPayment p WHERE p.qr_id = q.qr_id)

Another way

SELECT qr_id, merchant_id, location_label FROM QrCode WHERE is_closed = 0 AND qr_id NOT IN (SELECT qr_id FROM QrPayment)

← Razorpay: Merchants Whose KYC Documents Are All Unverified · Razorpay: Gateway Fee and Settled Amount for Each Payment →