Leads Never Taken on a Completed Site Visit

Easy
350.

Leads Never Taken on a Completed Site Visit

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

ColumnTypeValues
lead_idint
namevarchar
phonebigint
sourceenum99acres, MagicBricks, Housing, Walk-in, Referral

Primary key: lead_id.

One row per enquiry the sales team received.

Table: SiteVisit

ColumnTypeValues
visit_idint
lead_idint
visit_datedate
statusenumcompleted, cancelled, no_show

Primary key: visit_id.

lead_id is always a lead in SalesLead; a lead can have several visits.

Example

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

SalesLead

lead_idnamephonesource
1Aditi Sharma987654321099acres
2Rohan Iyer9123456780Walk-in
3Neha Gupta8899776655Referral
4Kabir Khan9988001122Housing
5Pooja Nair7766554433MagicBricks

SiteVisit

visit_idlead_idvisit_datestatus
1112025-01-11completed
1222025-01-12no_show
1322025-01-19cancelled
1442025-01-14cancelled
1542025-01-21completed

Output

lead_idname
2Rohan Iyer
3Neha Gupta
5Pooja Nair
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.

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

ColumnType
lead_idint
namevarchar
phonebigint
sourceenum(99acres, MagicBricks, Housing, Walk-in, Referral)

Primary key: lead_id.

One row per enquiry the sales team received.

Table: SiteVisit

ColumnType
visit_idint
lead_idint
visit_datedate
statusenum(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_idnamephonesource
1Aditi Sharma987654321099acres
2Rohan Iyer9123456780Walk-in
3Neha Gupta8899776655Referral
4Kabir Khan9988001122Housing
5Pooja Nair7766554433MagicBricks

SiteVisit

visit_idlead_idvisit_datestatus
1112025-01-11completed
1222025-01-12no_show
1322025-01-19cancelled
1442025-01-14cancelled
1542025-01-21completed

Output

lead_idname
2Rohan Iyer
3Neha Gupta
5Pooja 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 NULL

Another 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 →