Leads Never Taken on a Completed Site Visit — SQL Problem
A builder's sales team logs every site visit it schedules for a lead. A visit can be completed, cancelled or a noshow.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #350
Problem statement
A builder's sales team logs every site visit it schedules for a lead. A visit can be completed, cancelled or a no_show. The sales head wants to call back every lead who has not yet completed a single site visit — including leads who never had a visit scheduled.
Return lead_id and name of those leads, in any order. A lead with only cancelled or no-show visits is in the answer; a lead with at least one completed visit is not.
Tables
Table: SalesLead
| Column | Type |
|---|---|
| lead_id | int |
| name | varchar |
| phone | bigint |
| source | enum(99acres, MagicBricks, Housing, Walk-in, Referral) |
Primary key: lead_id.
One row per enquiry the sales team received.
Table: SiteVisit
| Column | Type |
|---|---|
| visit_id | int |
| lead_id | int |
| visit_date | date |
| status | enum(completed, cancelled, no_show) |
Primary key: visit_id.
lead_id is always a lead in SalesLead; a lead can have several visits.
Examples
Example 1
SalesLead
| lead_id | name | phone | source |
|---|---|---|---|
| 1 | Aditi Sharma | 9876543210 | 99acres |
| 2 | Rohan Iyer | 9123456780 | Walk-in |
| 3 | Neha Gupta | 8899776655 | Referral |
| 4 | Kabir Khan | 9988001122 | Housing |
| 5 | Pooja Nair | 7766554433 | MagicBricks |
SiteVisit
| visit_id | lead_id | visit_date | status |
|---|---|---|---|
| 11 | 1 | 2025-01-11 | completed |
| 12 | 2 | 2025-01-12 | no_show |
| 13 | 2 | 2025-01-19 | cancelled |
| 14 | 4 | 2025-01-14 | cancelled |
| 15 | 4 | 2025-01-21 | completed |
Output
| lead_id | name |
|---|---|
| 2 | Rohan Iyer |
| 3 | Neha Gupta |
| 5 | Pooja Nair |
How to solve Leads Never Taken on a Completed Site Visit
The question is an anti join: leads for which no completed visit exists. With a LEFT JOIN, the condition on the visit's status must sit in the ON clause, not in WHERE. In ON, it narrows which visits can match; a lead whose visits are all cancelled or no-show simply finds no match and comes through with NULLs, and v.visit_id IS NULL keeps it. Put v.status = 'completed' in WHERE instead and those NULL rows fail the test — the LEFT JOIN silently turns into an inner join and the answer is empty.
NOT EXISTS with the status in the subquery states the rule most directly. NOT IN is safe here because SiteVisit.lead_id is never NULL; if it could be, one NULL in the list would make every comparison unknown. Each form is one indexed lookup per lead with an index on SiteVisit(lead_id, status).
Reference solution (MySQL)
SELECT l.lead_id, l.name
FROM SalesLead l
LEFT JOIN SiteVisit v
ON v.lead_id = l.lead_id AND v.status = 'completed'
WHERE v.visit_id IS NULLAnother way
SELECT lead_id, name FROM SalesLead l WHERE NOT EXISTS (SELECT 1 FROM SiteVisit v WHERE v.lead_id = l.lead_id AND v.status = 'completed')Another way
SELECT lead_id, name FROM SalesLead WHERE lead_id NOT IN (SELECT lead_id FROM SiteVisit WHERE status = 'completed')← Rate per Square Foot by Locality · Masked Tenant Phone Numbers for the Notice Board →