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
| Column | Type |
|---|---|
| customer_id | int |
| full_name | varchar |
| city | varchar |
Primary key: customer_id.
One row per customer of the bank.
Table: KycDocument
| Column | Type |
|---|---|
| doc_id | int |
| customer_id | int |
| doc_type | enum(PAN, AADHAAR, PASSPORT) |
| status | enum(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_id | full_name | city |
|---|---|---|
| 1 | Aarav Sharma | Pune |
| 2 | Diya Nair | Kochi |
| 3 | Ishaan Reddy | Hyderabad |
| 4 | Kavya Iyer | Chennai |
| 5 | Rohan Gupta | Delhi |
KycDocument
| doc_id | customer_id | doc_type | status |
|---|---|---|---|
| 1 | 1 | PAN | VERIFIED |
| 2 | 2 | AADHAAR | REJECTED |
| 3 | 2 | PAN | PENDING |
| 4 | 4 | PASSPORT | REJECTED |
| 5 | 4 | AADHAAR | VERIFIED |
| 6 | 1 | AADHAAR | PENDING |
Output
| customer_id | full_name |
|---|---|
| 2 | Diya Nair |
| 3 | Ishaan Reddy |
| 5 | Rohan 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 NULLAnother 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 →