Enquiry-to-Token Funnel by Lead Source — SQL Joins Problem
A developer measures its sales funnel per lead source: enquiry → site tour → token payment.
- Difficulty: Hard
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #368
Problem statement
A developer measures its sales funnel per lead source: enquiry → site tour → token payment. A stage only counts in order: an enquiry has toured if it has a tour on or after its enquired_on date, and has tokened if it paid a token on or after its first such tour. A token paid without a tour before it (an NRI booking over video call) does not count as tokened.
Return one row per source with source, enquiries, toured, tokened and tour_to_token_pct = 100 × tokened ÷ toured rounded to 1 decimal (NULL when nothing toured). Order by enquiries descending, then source.
Tables
Table: Enquiry
| Column | Type |
|---|---|
| enquiry_id | int |
| source | enum(99acres, MagicBricks, Housing, Walk-in, Referral) |
| enquired_on | date |
Primary key: enquiry_id.
Table: SiteTour
| Column | Type |
|---|---|
| tour_id | int |
| enquiry_id | int |
| toured_on | date |
Primary key: tour_id.
An enquiry can tour the site several times. A tour dated before the enquiry is a data-entry slip and does not count.
Table: TokenPayment
| Column | Type |
|---|---|
| token_id | int |
| enquiry_id | int |
| paid_on | date |
| amount | int |
Primary key: token_id.
A token is the booking advance; an enquiry may pay it in parts.
Examples
Example 1
Enquiry
| enquiry_id | source | enquired_on |
|---|---|---|
| 1 | 99acres | 2025-01-02 |
| 2 | 99acres | 2025-01-05 |
| 3 | 99acres | 2025-01-07 |
| 4 | Referral | 2025-01-03 |
| 5 | Referral | 2025-01-09 |
| 6 | Walk-in | 2025-01-10 |
| 7 | Housing | 2025-01-12 |
SiteTour
| tour_id | enquiry_id | toured_on |
|---|---|---|
| 1 | 1 | 2025-01-06 |
| 2 | 1 | 2025-01-20 |
| 3 | 2 | 2025-01-11 |
| 4 | 4 | 2025-01-08 |
| 5 | 6 | 2025-01-10 |
| 6 | 7 | 2025-01-10 |
TokenPayment
| token_id | enquiry_id | paid_on | amount |
|---|---|---|---|
| 1 | 1 | 2025-01-20 | 100000 |
| 2 | 1 | 2025-01-25 | 150000 |
| 3 | 3 | 2025-01-15 | 200000 |
| 4 | 4 | 2025-01-08 | 100000 |
| 5 | 6 | 2025-01-09 | 50000 |
Output
| source | enquiries | toured | tokened | tour_to_token_pct |
|---|---|---|---|---|
| 99acres | 3 | 2 | 1 | 50 |
| Referral | 2 | 1 | 1 | 100 |
| Housing | 1 | 0 | 0 | NULL |
| Walk-in | 1 | 1 | 0 | 0 |
How to solve Enquiry-to-Token Funnel by Lead Source
A funnel is easiest to get right one entity at a time: reduce each enquiry to a row of 0/1 stage flags, then aggregate the flags. The first CTE finds each enquiry's first valid tour — MIN(toured_on) over tours dated on or after the enquiry, so a tour typed in before the enquiry (a slip) never counts. The second CTE LEFT JOINs that to every enquiry: no first tour means toured = 0, and tokened is an EXISTS for a token paid on or after the first tour. When the first tour is NULL, the comparison is unknown and the flag is 0 — a token without a prior tour is not counted, which is the in-order rule.
In the example, enquiry 3 paid a token but never toured; enquiry 6 paid the day before its tour; enquiry 7's only tour is dated before the enquiry. None of those advance. Enquiry 4 toured and paid on the same day — in order, so it counts.
Joining tours and tokens straight onto enquiries multiplies rows (two tours × two token parts), so the alternative pre-aggregates the first tour and then counts DISTINCT enquiry ids at each stage. NULLIF keeps a source with no tours from dividing by zero.
Reference solution (MySQL)
WITH first_tour AS (
SELECT e.enquiry_id, MIN(t.toured_on) AS first_tour_on
FROM Enquiry e
JOIN SiteTour t ON t.enquiry_id = e.enquiry_id AND t.toured_on >= e.enquired_on
GROUP BY e.enquiry_id
), stages AS (
SELECT e.enquiry_id, e.source,
CASE WHEN f.enquiry_id IS NULL THEN 0 ELSE 1 END AS toured,
CASE WHEN EXISTS (
SELECT 1 FROM TokenPayment p
WHERE p.enquiry_id = e.enquiry_id AND p.paid_on >= f.first_tour_on
) THEN 1 ELSE 0 END AS tokened
FROM Enquiry e
LEFT JOIN first_tour f ON f.enquiry_id = e.enquiry_id
)
SELECT source,
COUNT(*) AS enquiries,
SUM(toured) AS toured,
SUM(tokened) AS tokened,
ROUND(100 * SUM(tokened) / NULLIF(SUM(toured), 0), 1) AS tour_to_token_pct
FROM stages
GROUP BY source
ORDER BY enquiries DESC, sourceAnother way
SELECT e.source, COUNT(DISTINCT e.enquiry_id) AS enquiries,
COUNT(DISTINCT t.enquiry_id) AS toured,
COUNT(DISTINCT p.enquiry_id) AS tokened,
ROUND(100 * COUNT(DISTINCT p.enquiry_id) / NULLIF(COUNT(DISTINCT t.enquiry_id), 0), 1) AS tour_to_token_pct
FROM Enquiry e
LEFT JOIN (SELECT s.enquiry_id, MIN(s.toured_on) AS first_on FROM SiteTour s JOIN Enquiry q ON q.enquiry_id = s.enquiry_id WHERE s.toured_on >= q.enquired_on GROUP BY s.enquiry_id) t ON t.enquiry_id = e.enquiry_id
LEFT JOIN TokenPayment p ON p.enquiry_id = t.enquiry_id AND p.paid_on >= t.first_on
GROUP BY e.source
ORDER BY enquiries DESC, e.source← Median Registered Rate per Sq Ft by Locality · Double-Booked Beds in a Co-Living Space →