Razorpay: Merchants Whose KYC Documents Are All Unverified
The merchant onboarding team at Razorpay works a queue of businesses that have started KYC but cannot go live yet, because not one of their documents has…
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #900
Problem statement
The merchant onboarding team at Razorpay works a queue of businesses that have started KYC but cannot go live yet, because not one of their documents has been verified.
Return every merchant that has submitted at least one KYC document and has no verified document (every one of its documents has verified_on NULL). Return merchant_id, business_name and documents, the number of documents the merchant submitted. Merchants who submitted nothing are not in the queue. 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: Merchant
| Column | Type |
|---|---|
| merchant_id | int |
| business_name | varchar |
| signed_up_on | date |
Primary key: merchant_id.
One row per business that created a Razorpay account.
Table: KycDocument
| Column | Type |
|---|---|
| doc_id | int |
| merchant_id | int |
| doc_type | enum(PAN, GSTIN, bank_proof, address_proof) |
| submitted_on | date |
| verified_on | date |
Primary key: doc_id.
One row per uploaded document; verified_on is NULL until an onboarding analyst approves it.
Examples
Example 1
Merchant
| merchant_id | business_name | signed_up_on |
|---|---|---|
| 1 | Kirana Kart | 2025-01-04 |
| 2 | Urban Threads | 2025-01-09 |
| 3 | FitFuel Labs | 2025-01-12 |
| 4 | BookNook | 2025-01-15 |
| 5 | TrekNest | 2025-01-20 |
KycDocument
| doc_id | merchant_id | doc_type | submitted_on | verified_on |
|---|---|---|---|---|
| 10 | 1 | PAN | 2025-01-05 | 2025-01-07 |
| 11 | 1 | GSTIN | 2025-01-05 | NULL |
| 12 | 2 | PAN | 2025-01-10 | NULL |
| 13 | 2 | bank_proof | 2025-01-11 | NULL |
| 14 | 4 | PAN | 2025-01-16 | NULL |
| 15 | 5 | GSTIN | 2025-01-21 | 2025-01-22 |
Output
| merchant_id | business_name | documents |
|---|---|---|
| 2 | Urban Threads | 2 |
| 4 | BookNook | 1 |
How to solve Razorpay: Merchants Whose KYC Documents Are All Unverified
The condition is about all of a merchant's documents at once, so it belongs to a group, not to a row. Join merchants to their documents (an inner join, which removes merchants with no documents — they are not in the queue) and group by merchant. COUNT(*) is then the number of documents submitted.
COUNT(d.verified_on) counts only the documents that carry a verification date. If it is 0, no document of that merchant is verified, which is exactly the queue's rule; HAVING keeps those groups. Filtering verified_on IS NULL in WHERE instead would be wrong: a merchant with one verified and one pending document would still show up with its pending row.
The alternative states the rule literally with EXISTS (submitted something) and NOT EXISTS (nothing verified), plus a scalar subquery for the count. Both read each document once with an index on KycDocument(merchant_id).
Reference solution (MySQL)
SELECT m.merchant_id, m.business_name, COUNT(*) AS documents
FROM Merchant m
JOIN KycDocument d ON d.merchant_id = m.merchant_id
GROUP BY m.merchant_id, m.business_name
HAVING COUNT(d.verified_on) = 0Another way
SELECT m.merchant_id, m.business_name,
(SELECT COUNT(*) FROM KycDocument d WHERE d.merchant_id = m.merchant_id) AS documents
FROM Merchant m
WHERE EXISTS (SELECT 1 FROM KycDocument d WHERE d.merchant_id = m.merchant_id)
AND NOT EXISTS (SELECT 1 FROM KycDocument d WHERE d.merchant_id = m.merchant_id AND d.verified_on IS NOT NULL)← Razorpay: Captured Volume by Payment Method per Merchant · Razorpay: Open QR Codes That Never Received a Payment →