Razorpay: Merchants Whose KYC Documents Are All Unverified

Easy
900.

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

ColumnType
merchant_idint
business_namevarchar
signed_up_ondate

Primary key: merchant_id.

One row per business that created a Razorpay account.

Table: KycDocument

ColumnTypeValues
doc_idint
merchant_idint
doc_typeenumPAN, GSTIN, bank_proof, address_proof
submitted_ondate
verified_ondate

Primary key: doc_id.

One row per uploaded document; verified_on is NULL until an onboarding analyst approves it.

Example

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

Merchant

merchant_idbusiness_namesigned_up_on
1Kirana Kart2025-01-04
2Urban Threads2025-01-09
3FitFuel Labs2025-01-12
4BookNook2025-01-15
5TrekNest2025-01-20

KycDocument

doc_idmerchant_iddoc_typesubmitted_onverified_on
101PAN2025-01-052025-01-07
111GSTIN2025-01-05NULL
122PAN2025-01-10NULL
132bank_proof2025-01-11NULL
144PAN2025-01-16NULL
155GSTIN2025-01-212025-01-22

Output

merchant_idbusiness_namedocuments
2Urban Threads2
4BookNook1
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: 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

ColumnType
merchant_idint
business_namevarchar
signed_up_ondate

Primary key: merchant_id.

One row per business that created a Razorpay account.

Table: KycDocument

ColumnType
doc_idint
merchant_idint
doc_typeenum(PAN, GSTIN, bank_proof, address_proof)
submitted_ondate
verified_ondate

Primary key: doc_id.

One row per uploaded document; verified_on is NULL until an onboarding analyst approves it.

Examples

Example 1

Merchant

merchant_idbusiness_namesigned_up_on
1Kirana Kart2025-01-04
2Urban Threads2025-01-09
3FitFuel Labs2025-01-12
4BookNook2025-01-15
5TrekNest2025-01-20

KycDocument

doc_idmerchant_iddoc_typesubmitted_onverified_on
101PAN2025-01-052025-01-07
111GSTIN2025-01-05NULL
122PAN2025-01-10NULL
132bank_proof2025-01-11NULL
144PAN2025-01-16NULL
155GSTIN2025-01-212025-01-22

Output

merchant_idbusiness_namedocuments
2Urban Threads2
4BookNook1

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) = 0

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