Days From First Site Visit to Booking — SQL Joins Problem
A builder's CRM records leads, their site visits and unit bookings (a lead books at most one unit).
- Difficulty: Medium
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #362
Problem statement
A builder's CRM records leads, their site visits and unit bookings (a lead books at most one unit). Sales wants the time to close: for each lead that booked and had at least one site visit on or before the booking date, how long it took from their first visit.
Return lead_id, name, first_visit, booked_on, days_to_book (days from first_visit to booked_on) and visits_before_booking (visits on or before booked_on). Visits after the booking (handover walkthroughs) are ignored. Order by days_to_book, then lead_id.
Tables
Table: BuyerLead
| Column | Type |
|---|---|
| lead_id | int |
| name | varchar |
| source | enum(99acres, MagicBricks, Housing, Walk-in, Referral) |
Primary key: lead_id.
Table: LeadVisit
| Column | Type |
|---|---|
| visit_id | int |
| lead_id | int |
| visit_date | date |
Primary key: visit_id.
One row per completed site visit.
Table: UnitBooking
| Column | Type |
|---|---|
| booking_id | int |
| lead_id | int |
| unit_no | varchar |
| booked_on | date |
Primary key: booking_id.
At most one booking per lead.
Examples
Example 1
BuyerLead
| lead_id | name | source |
|---|---|---|
| 1 | Ananya Reddy | Housing |
| 2 | Vivaan Joshi | Walk-in |
| 3 | Mia Patel | Referral |
| 4 | Nikhil Bose | 99acres |
| 5 | Riya Singh | MagicBricks |
LeadVisit
| visit_id | lead_id | visit_date |
|---|---|---|
| 1 | 1 | 2025-01-04 |
| 2 | 1 | 2025-01-18 |
| 3 | 2 | 2025-01-10 |
| 4 | 2 | 2025-02-20 |
| 5 | 3 | 2025-01-12 |
| 6 | 4 | 2025-02-01 |
| 7 | 5 | 2025-02-02 |
UnitBooking
| booking_id | lead_id | unit_no | booked_on |
|---|---|---|---|
| 1 | 1 | T2-1103 | 2025-01-25 |
| 2 | 2 | T1-0704 | 2025-01-10 |
| 3 | 4 | T3-0201 | 2025-01-28 |
| 4 | 5 | T2-0905 | 2025-02-16 |
Output
| lead_id | name | first_visit | booked_on | days_to_book | visits_before_booking |
|---|---|---|---|---|---|
| 2 | Vivaan Joshi | 2025-01-10 | 2025-01-10 | 0 | 1 |
| 5 | Riya Singh | 2025-02-02 | 2025-02-16 | 14 | 1 |
| 1 | Ananya Reddy | 2025-01-04 | 2025-01-25 | 21 | 2 |
How to solve Days From First Site Visit to Booking
Join three tables and aggregate once. Each lead with a booking is joined to its visits, with the condition v.visit_date <= b.booked_on in the join so that post-booking walkthroughs never match. Grouping by the lead (and the booking date, which is one value per lead) gives MIN(visit_date) as the first visit and COUNT(*) as the visits before booking; DATEDIFF of the two dates is the time to close.
Inner joins do the exclusions for free: a lead with no booking (Mia) never joins UnitBooking, and a lead whose only visit came after the booking (Nikhil) finds no qualifying visit. A visit on the booking day itself counts — Vivaan visited and booked on the 10th, so his time to close is 0 and his later visit is ignored.
Correlated subqueries for the first visit and the count work too, with an outer filter dropping leads whose first visit is NULL. With an index on LeadVisit(lead_id, visit_date) every form is one range read per booked lead.
Reference solution (MySQL)
SELECT l.lead_id, l.name,
MIN(v.visit_date) AS first_visit,
b.booked_on,
DATEDIFF(b.booked_on, MIN(v.visit_date)) AS days_to_book,
COUNT(*) AS visits_before_booking
FROM BuyerLead l
JOIN UnitBooking b ON b.lead_id = l.lead_id
JOIN LeadVisit v ON v.lead_id = l.lead_id AND v.visit_date <= b.booked_on
GROUP BY l.lead_id, l.name, b.booked_on
ORDER BY days_to_book, l.lead_idAnother way
SELECT * FROM (SELECT l.lead_id, l.name, (SELECT MIN(v.visit_date) FROM LeadVisit v WHERE v.lead_id = l.lead_id AND v.visit_date <= b.booked_on) AS first_visit, b.booked_on, DATEDIFF(b.booked_on, (SELECT MIN(v.visit_date) FROM LeadVisit v WHERE v.lead_id = l.lead_id AND v.visit_date <= b.booked_on)) AS days_to_book, (SELECT COUNT(*) FROM LeadVisit v WHERE v.lead_id = l.lead_id AND v.visit_date <= b.booked_on) AS visits_before_booking FROM BuyerLead l JOIN UnitBooking b ON b.lead_id = l.lead_id) t WHERE first_visit IS NOT NULL ORDER BY days_to_book, lead_idAnother way
WITH pre AS (SELECT b.lead_id, b.booked_on, v.visit_date FROM UnitBooking b JOIN LeadVisit v ON v.lead_id = b.lead_id WHERE v.visit_date <= b.booked_on) SELECT l.lead_id, l.name, MIN(p.visit_date) AS first_visit, MAX(p.booked_on) AS booked_on, DATEDIFF(MAX(p.booked_on), MIN(p.visit_date)) AS days_to_book, COUNT(*) AS visits_before_booking FROM pre p JOIN BuyerLead l ON l.lead_id = p.lead_id GROUP BY l.lead_id, l.name ORDER BY days_to_book, l.lead_id← Parse Listing Titles Into Locality and BHK · Co-Living Bed Occupancy by Property →