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
| Column | Type |
|---|---|
| qr_id | int |
| merchant_id | int |
| location_label | varchar |
| created_on | date |
| is_closed | bool |
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
| Column | Type |
|---|---|
| payment_id | int |
| qr_id | int |
| amount | int |
| received_at | datetime |
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_id | merchant_id | location_label | created_on | is_closed |
|---|---|---|---|---|
| 1 | 201 | Billing Desk | 2025-02-01 | 0 |
| 2 | 201 | Counter 2 | 2025-02-01 | 0 |
| 3 | 202 | Food Court Kiosk | 2025-02-10 | 1 |
| 4 | 203 | Delivery Van 3 | 2025-02-12 | 0 |
| 5 | 203 | Pharmacy Counter | 2025-02-12 | 0 |
QrPayment
| payment_id | qr_id | amount | received_at |
|---|---|---|---|
| 9001 | 1 | 240 | 2025-02-03 11:20:00 |
| 9002 | 1 | 85 | 2025-02-03 12:05:00 |
| 9003 | 4 | 1200 | 2025-02-14 18:40:00 |
Output
| qr_id | merchant_id | location_label |
|---|---|---|
| 2 | 201 | Counter 2 |
| 5 | 203 | Pharmacy 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 NULLAnother 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 →