Customers Without a Verified KYC Document

Easy
49.

Customers Without a Verified KYC Document

RBI rules let a customer transact only after at least one officially valid document has been verified. Customers upload PAN, Aadhaar or passport copies, and each upload is reviewed separately.

Return customer_id and full_name of every customer who has no document with status VERIFIED — whether they uploaded only pending or rejected documents or nothing at all. Return the rows in any order.

Tables

Table: Customer

ColumnType
customer_idint
full_namevarchar
cityvarchar

Primary key: customer_id.

One row per customer of the bank.

Table: KycDocument

ColumnTypeValues
doc_idint
customer_idint
doc_typeenumPAN, AADHAAR, PASSPORT
statusenumVERIFIED, PENDING, REJECTED

Primary key: doc_id.

One row per uploaded document; customer_id always names a row of Customer.

Example

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

Customer

customer_idfull_namecity
1Aarav SharmaPune
2Diya NairKochi
3Ishaan ReddyHyderabad
4Kavya IyerChennai
5Rohan GuptaDelhi

KycDocument

doc_idcustomer_iddoc_typestatus
11PANVERIFIED
22AADHAARREJECTED
32PANPENDING
44PASSPORTREJECTED
54AADHAARVERIFIED
61AADHAARPENDING

Output

customer_idfull_name
2Diya Nair
3Ishaan Reddy
5Rohan Gupta
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.

Customers Without a Verified KYC Document — SQL Problem

RBI rules let a customer transact only after at least one officially valid document has been verified.

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

Problem statement

RBI rules let a customer transact only after at least one officially valid document has been verified. Customers upload PAN, Aadhaar or passport copies, and each upload is reviewed separately.

Return customer_id and full_name of every customer who has no document with status VERIFIED — whether they uploaded only pending or rejected documents or nothing at all. Return the rows in any order.

Tables

Table: Customer

ColumnType
customer_idint
full_namevarchar
cityvarchar

Primary key: customer_id.

One row per customer of the bank.

Table: KycDocument

ColumnType
doc_idint
customer_idint
doc_typeenum(PAN, AADHAAR, PASSPORT)
statusenum(VERIFIED, PENDING, REJECTED)

Primary key: doc_id.

One row per uploaded document; customer_id always names a row of Customer.

Examples

Example 1

Customer

customer_idfull_namecity
1Aarav SharmaPune
2Diya NairKochi
3Ishaan ReddyHyderabad
4Kavya IyerChennai
5Rohan GuptaDelhi

KycDocument

doc_idcustomer_iddoc_typestatus
11PANVERIFIED
22AADHAARREJECTED
32PANPENDING
44PASSPORTREJECTED
54AADHAARVERIFIED
61AADHAARPENDING

Output

customer_idfull_name
2Diya Nair
3Ishaan Reddy
5Rohan Gupta

How to solve Customers Without a Verified KYC Document

This is an anti join with a condition on the partner rows: keep the customers for whom no verified document exists. The cleanest LEFT JOIN form puts the status test inside the ON clause, so the join only ever matches verified documents; a customer with none gets a single row of NULLs, which k.doc_id IS NULL keeps.

Moving k.status = 'VERIFIED' into the WHERE clause instead would be a bug: it would throw away the NULL rows of unmatched customers and keep only customers who do have a verified document — the opposite of the question. A customer with a rejected and a pending upload would also vanish.

NOT EXISTS states the rule directly and is usually the plan an optimiser picks. NOT IN is safe here because customer_id in the documents is never NULL. A grouped version counts verified documents per customer and keeps the zeros. Each form is one pass over customers with an index lookup on KycDocument(customer_id).

Reference solution (MySQL)

SELECT c.customer_id, c.full_name
FROM Customer c
LEFT JOIN KycDocument k ON k.customer_id = c.customer_id AND k.status = 'VERIFIED'
WHERE k.doc_id IS NULL

Another way

SELECT customer_id, full_name FROM Customer c WHERE NOT EXISTS (SELECT 1 FROM KycDocument k WHERE k.customer_id = c.customer_id AND k.status = 'VERIFIED')

Another way

SELECT customer_id, full_name FROM Customer WHERE customer_id NOT IN (SELECT customer_id FROM KycDocument WHERE status = 'VERIFIED')

Another way

SELECT c.customer_id, c.full_name FROM Customer c LEFT JOIN KycDocument k ON k.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
HAVING SUM(CASE WHEN k.status = 'VERIFIED' THEN 1 ELSE 0 END) = 0 OR COUNT(k.doc_id) = 0

← High-Value UPI Transfers for Compliance Review · Active Deposits Held at Each Branch →