Freshworks hard SUBSTRINSTRGROUP BYCOUNT

For leads that have a valid, well-formed email address (one that actually contains '@'), extract the domain — everything after the '@' — using SUBSTR and INSTR. Exclude leads with no email at all, exclude leads whose email has no '@' at all (INSTR returns 0 for those, and SUBSTR would otherwise silently treat the malformed string as its own domain), and exclude personal email providers ('gmail.com', 'yahoo.com', 'hotmail.com', 'outlook.com'). Return domain and lead_count — the number of leads at that domain — grouped by domain, ordered alphabetically by domain.

Freshworks · Lead Email Domain Parsing — practice this real-world SQL scenario live in your browser.

📖 Story Freshworks · Lead Email Domain Parsing

Freshworks' Sales Ops team, ahead of a board review, needs a report of "business leads by company email domain" — but the leads table doubles as a catch-all for every inbound channel: cold calls where only a phone number was captured (email genuinely NULL, not yet collected), and a trade-show badge-scanner that occasionally mangles an email during OCR, dropping the '@' entirely and leaving something that looks like a domain but isn't a real email at all. Personal-provider addresses (gmail.com, yahoo.com, etc.) don't count as a "company" for this report either — leadership only wants to see actual business domains that sales could plausibly qualify.

🎯 Your Mission

📋 Table Structure

🗂 leads

lead_id INTEGER 1

name TEXT Priya Sharma

email TEXT priya.sharma@freshdesk.io

phone TEXT 9876543210

source TEXT Signup Form

⚡ Step-by-Step Walkthrough

Look at every lead's email and where '@' sits in it

query.sql

SELECT lead_id, name, email, INSTR(email, '@') AS at_position, phone
FROM leads
ORDER BY lead_id;

💡 Explanation

INSTR(email, '@') returns the 1-based position of '@' inside the string — Priya's email has '@' at position 13, Divya's at position 6, and so on; this is the building block the rest of the query relies on.
Karthik Iyer (lead_id 4) has a genuinely NULL email — he came in through a cold call and only gave a phone number, so INSTR(NULL, '@') itself returns NULL, not 0 or an error.
Rohan Malhotra's (lead_id 6) email is 'rohanmalhotracorp.com' — a trade-show badge scan mangled it and dropped the '@' entirely — so INSTR returns 0, not NULL; 0 and NULL are two different signals for two different problems and need to be handled differently.
Nothing is extracted or filtered yet — this step exists purely to see, row by row, which leads have a real '@', which have none, and which have no email at all.

The trap: filtering NULL isn't enough

query.sql

SELECT SUBSTR(email, INSTR(email,'@')+1) AS domain, COUNT(*) AS lead_count
FROM leads
WHERE email IS NOT NULL
GROUP BY domain
ORDER BY lead_count DESC, domain;

💡 Explanation

WHERE email IS NOT NULL correctly drops Karthik Iyer's row, so that part of the fix looks solid at a glance.
Rohan Malhotra's row survives the filter, because his email isn't NULL — it's just malformed — and INSTR('rohanmalhotracorp.com', '@') returns 0, not NULL.
SUBSTR(email, 0+1) is SUBSTR(email, 1), which returns the entire original string starting from its first character — so Rohan's whole mangled email becomes its own fake 'domain,' sitting in the results as if it were a real company.
gmail.com shows up with a count of 2, mixed in right alongside genuine business domains like freshdesk.io and zylker.com — this query never asked whether a domain belongs to a personal provider, so it can't tell them apart yet.

Fix it: guard against INSTR = 0 and drop personal domains

query.sql

SELECT SUBSTR(email, INSTR(email,'@')+1) AS domain, COUNT(*) AS lead_count
FROM leads
WHERE email IS NOT NULL
  AND INSTR(email,'@') > 0
  AND SUBSTR(email, INSTR(email,'@')+1) NOT IN ('gmail.com','yahoo.com','hotmail.com','outlook.com')
GROUP BY domain
ORDER BY domain;

💡 Explanation

AND INSTR(email,'@') > 0 is the fix step 2 was missing — it explicitly rejects Rohan Malhotra's malformed email, because a 0 means 'no @ found at all,' not 'found one at the very start.'
WHERE email IS NOT NULL and INSTR(email,'@') > 0 together cover both failure modes from step 1: a lead with no email address at all, and a lead with an email string that isn't shaped like one.
The NOT IN list removes gmail.com and yahoo.com, so Arjun, Divya, and Aditya's personal-provider leads drop out of the count entirely, leaving only domains that plausibly belong to an actual company.
Three domains survive: freshdesk.io, techflow.io, and zylker.com, each with lead_count = 1 — a small, clean result that's the direct payoff of stacking three separate filters instead of trusting any single one of them alone.

▶ Practice this live ← Browse all questions