Days From First Site Visit to Booking

Medium
362.

Days From First Site Visit to Booking

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

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

Primary key: lead_id.

Table: LeadVisit

ColumnType
visit_idint
lead_idint
visit_datedate

Primary key: visit_id.

One row per completed site visit.

Table: UnitBooking

ColumnType
booking_idint
lead_idint
unit_novarchar
booked_ondate

Primary key: booking_id.

At most one booking per lead.

Example

The rows must come back in the order shown.

BuyerLead

lead_idnamesource
1Ananya ReddyHousing
2Vivaan JoshiWalk-in
3Mia PatelReferral
4Nikhil Bose99acres
5Riya SinghMagicBricks

LeadVisit

visit_idlead_idvisit_date
112025-01-04
212025-01-18
322025-01-10
422025-02-20
532025-01-12
642025-02-01
752025-02-02

UnitBooking

booking_idlead_idunit_nobooked_on
11T2-11032025-01-25
22T1-07042025-01-10
34T3-02012025-01-28
45T2-09052025-02-16

Output

lead_idnamefirst_visitbooked_ondays_to_bookvisits_before_booking
2Vivaan Joshi2025-01-102025-01-1001
5Riya Singh2025-02-022025-02-16141
1Ananya Reddy2025-01-042025-01-25212
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.

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

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

Primary key: lead_id.

Table: LeadVisit

ColumnType
visit_idint
lead_idint
visit_datedate

Primary key: visit_id.

One row per completed site visit.

Table: UnitBooking

ColumnType
booking_idint
lead_idint
unit_novarchar
booked_ondate

Primary key: booking_id.

At most one booking per lead.

Examples

Example 1

BuyerLead

lead_idnamesource
1Ananya ReddyHousing
2Vivaan JoshiWalk-in
3Mia PatelReferral
4Nikhil Bose99acres
5Riya SinghMagicBricks

LeadVisit

visit_idlead_idvisit_date
112025-01-04
212025-01-18
322025-01-10
422025-02-20
532025-01-12
642025-02-01
752025-02-02

UnitBooking

booking_idlead_idunit_nobooked_on
11T2-11032025-01-25
22T1-07042025-01-10
34T3-02012025-01-28
45T2-09052025-02-16

Output

lead_idnamefirst_visitbooked_ondays_to_bookvisits_before_booking
2Vivaan Joshi2025-01-102025-01-1001
5Riya Singh2025-02-022025-02-16141
1Ananya Reddy2025-01-042025-01-25212

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_id

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

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