Enquiry-to-Token Funnel by Lead Source

Hard
368.

Enquiry-to-Token Funnel by Lead Source

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

ColumnTypeValues
enquiry_idint
sourceenum99acres, MagicBricks, Housing, Walk-in, Referral
enquired_ondate

Primary key: enquiry_id.

Table: SiteTour

ColumnType
tour_idint
enquiry_idint
toured_ondate

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

ColumnType
token_idint
enquiry_idint
paid_ondate
amountint

Primary key: token_id.

A token is the booking advance; an enquiry may pay it in parts.

Example

The rows must come back in the order shown.

Enquiry

enquiry_idsourceenquired_on
199acres2025-01-02
299acres2025-01-05
399acres2025-01-07
4Referral2025-01-03
5Referral2025-01-09
6Walk-in2025-01-10
7Housing2025-01-12

SiteTour

tour_idenquiry_idtoured_on
112025-01-06
212025-01-20
322025-01-11
442025-01-08
562025-01-10
672025-01-10

TokenPayment

token_idenquiry_idpaid_onamount
112025-01-20100000
212025-01-25150000
332025-01-15200000
442025-01-08100000
562025-01-0950000

Output

sourceenquiriestouredtokenedtour_to_token_pct
99acres32150
Referral211100
Housing100NULL
Walk-in1100
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.

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

ColumnType
enquiry_idint
sourceenum(99acres, MagicBricks, Housing, Walk-in, Referral)
enquired_ondate

Primary key: enquiry_id.

Table: SiteTour

ColumnType
tour_idint
enquiry_idint
toured_ondate

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

ColumnType
token_idint
enquiry_idint
paid_ondate
amountint

Primary key: token_id.

A token is the booking advance; an enquiry may pay it in parts.

Examples

Example 1

Enquiry

enquiry_idsourceenquired_on
199acres2025-01-02
299acres2025-01-05
399acres2025-01-07
4Referral2025-01-03
5Referral2025-01-09
6Walk-in2025-01-10
7Housing2025-01-12

SiteTour

tour_idenquiry_idtoured_on
112025-01-06
212025-01-20
322025-01-11
442025-01-08
562025-01-10
672025-01-10

TokenPayment

token_idenquiry_idpaid_onamount
112025-01-20100000
212025-01-25150000
332025-01-15200000
442025-01-08100000
562025-01-0950000

Output

sourceenquiriestouredtokenedtour_to_token_pct
99acres32150
Referral211100
Housing100NULL
Walk-in1100

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, source

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