Zerodha mid LIKEWHEREIS NULLORDER BY

For each account in zerodha_clients where account_type is 'corporate', return client_name and email for accounts that either use a personal email domain (gmail.com, yahoo.com, or hotmail.com) or have no email on file at all. Sort by client_name.

Zerodha · Corporate KYC Screening — practice this real-world SQL scenario live in your browser.

📖 Story Zerodha · Corporate KYC Screening

Wednesday afternoon at Zerodha's Compliance desk. zerodha_clients holds every trading account — client_name, account_type ('individual' or 'corporate'), the email on file, and kyc_status. Company policy says a corporate trading account must be reachable at a proper company email, not a free personal inbox — gmail.com, yahoo.com, and hotmail.com addresses are all against policy for corporate accounts (individual accounts can use whatever email they like). Some corporate accounts don't even have an email on file yet — the relationship manager is still chasing it — and that email is a genuine NULL there, not an empty string or a made-up placeholder. The compliance team wants every corporate account that either uses a banned personal domain or has no email at all, so they can chase it down before the next audit.

🎯 Your Mission

📋 Table Structure

🗂 zerodha_clients

id INTEGER 1

client_name TEXT Aarav Enterprises

account_type TEXT corporate

email TEXT aarav.mehta@gmail.com

kyc_status TEXT pending

⚡ Step-by-Step Walkthrough

Look at the raw client list, corporate and individual mixed together

query.sql

SELECT *
FROM zerodha_clients
ORDER BY id;

💡 Explanation

zerodha_clients has 12 rows total: 9 corporate accounts and 3 individual accounts (Divya Traders, Gopal Singh, Kunal Verma) — the personal-email-domain policy only applies to the corporate ones.
Four corporate accounts already use personal domains right in the raw data — Aarav Enterprises and Lakshya Steel on gmail.com, Ekta Foods Pvt Ltd on yahoo.com, and Ishaan Logistics on hotmail.com.
Chetan & Co and Harsha Apparels are corporate accounts with email = NULL, not an empty string — the relationship manager simply hasn't collected it yet, and that's a genuine NULL, not a stand-in for anything.
Kunal Verma, an individual account, also has a NULL email — but individual accounts aren't covered by the corporate-domain policy at all, so that NULL should never show up in the final flagged list no matter how the query is written.

See the trap: LIKE alone silently drops the NULL emails

query.sql

SELECT client_name, email
FROM zerodha_clients
WHERE account_type = 'corporate'
  AND (email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com' OR email LIKE '%@hotmail.com')
ORDER BY client_name;

💡 Explanation

This returns exactly 4 rows — Aarav Enterprises, Ekta Foods Pvt Ltd, Ishaan Logistics, and Lakshya Steel — every corporate account whose email actually matches one of the three banned domains.
Chetan & Co and Harsha Apparels vanish completely, even though a missing email is arguably the bigger compliance gap of the two — you can't even verify a domain you don't have on file.
The reason is SQL's three-valued logic: email LIKE '%@gmail.com' doesn't evaluate to FALSE when email is NULL, it evaluates to NULL, and every OR chained onto a NULL email stays NULL too, so WHERE drops the row exactly like it would drop a genuine non-match.
This is easy to miss because the query looks complete — it checks all three banned domains — and it only breaks on the specific accounts where the relationship manager hasn't done their job yet, which is exactly the audit's blind spot.

Fix it: explicit IS NULL brings the missing-email accounts back in

query.sql

SELECT client_name, email
FROM zerodha_clients
WHERE account_type = 'corporate'
  AND (email LIKE '%@gmail.com' OR email LIKE '%@yahoo.com' OR email LIKE '%@hotmail.com' OR email IS NULL)
ORDER BY client_name;

💡 Explanation

Adding OR email IS NULL as its own explicit condition inside the parentheses restores Chetan & Co and Harsha Apparels, bringing the result to the full 6 rows the compliance team actually needs to review.
IS NULL is the only correct way to test for NULL in SQL — email = NULL would have the exact same silent-failure problem as the LIKE conditions, since = NULL also evaluates to NULL instead of TRUE, never matching anything.
account_type = 'corporate' still does its job correctly throughout, because none of the three individual accounts have account_type = 'corporate' — that comparison was never the problem, only the OR'd LIKE conditions on a nullable column were.
Kunal Verma's NULL email, on an individual account, is filtered out by account_type = 'corporate' before the email conditions are even evaluated — proving the fix only had to reach the corporate rows, not every NULL email in the table.

▶ Practice this live ← Browse all questions