Broker Site-Visit Conversion Rate — SQL Aggregation Problem
A developer's channel-partner team ranks brokers by how often a site visit they bring ends in a booking. Brokers with fewer than 3 visits are too new to rank.
- Difficulty: Medium
- Topics: Aggregation, Conditional Logic, Joins
- Dialect: MySQL
- Problem: #357
Problem statement
A developer's channel-partner team ranks brokers by how often a site visit they bring ends in a booking. Brokers with fewer than 3 visits are too new to rank.
For every broker with at least 3 visits, return broker_name, visits, bookings (visits with outcome 'booked') and conversion_pct = 100 × bookings ÷ visits, rounded to 1 decimal place. Order by conversion_pct descending, then broker_name ascending.
Tables
Table: Broker
| Column | Type |
|---|---|
| broker_id | int |
| broker_name | varchar |
| rera_no | varchar |
Primary key: broker_id.
Registered channel partners; broker names are unique.
Table: BrokerVisit
| Column | Type |
|---|---|
| visit_id | int |
| broker_id | int |
| visit_date | date |
| outcome | enum(booked, interested, not_interested, no_show) |
Primary key: visit_id.
One row per site visit a broker brought; broker_id is always in Broker.
Examples
Example 1
Broker
| broker_id | broker_name | rera_no |
|---|---|---|
| 1 | Sunil Realty | P52100011 |
| 2 | Kumar Estates | P52100023 |
| 3 | Prime Homes | P52100037 |
| 4 | Nest Brokers | P52100041 |
BrokerVisit
| visit_id | broker_id | visit_date | outcome |
|---|---|---|---|
| 1 | 1 | 2025-02-01 | booked |
| 2 | 1 | 2025-02-03 | interested |
| 3 | 1 | 2025-02-08 | no_show |
| 4 | 1 | 2025-02-09 | booked |
| 5 | 2 | 2025-02-02 | booked |
| 6 | 2 | 2025-02-05 | booked |
| 7 | 3 | 2025-02-04 | not_interested |
| 8 | 3 | 2025-02-06 | interested |
| 9 | 3 | 2025-02-10 | no_show |
| 10 | 2 | 2025-02-11 | not_interested |
| 11 | 2 | 2025-02-12 | booked |
| 12 | 2 | 2025-02-14 | interested |
Output
| broker_name | visits | bookings | conversion_pct |
|---|---|---|---|
| Kumar Estates | 5 | 3 | 60 |
| Sunil Realty | 4 | 2 | 50 |
| Prime Homes | 3 | 0 | 0 |
How to solve Broker Site-Visit Conversion Rate
Join each visit to its broker and group by broker: COUNT(*) is the visits, and conditional aggregation — SUM(CASE WHEN outcome = 'booked' THEN 1 ELSE 0 END) — counts the bookings in the same pass. The conversion rate is their ratio times 100, rounded once at the end; writing 100 * before the division keeps it a percentage rather than a fraction.
The 3-visit floor is a condition on the group, so it lives in HAVING, after the counts exist; a WHERE cannot see COUNT(*). Prime Homes has three visits and no booking — it qualifies with 0.0%, while a broker with two visits would be dropped however good they look.
Equivalent forms: pre-aggregate visits per broker in a derived table and join the names afterwards, or note that the average of a 100-or-0 flag is the percentage. Grouping by broker_id as well as the name keeps two brokers apart even if names repeated. One pass over the visits.
Reference solution (MySQL)
SELECT b.broker_name,
COUNT(*) AS visits,
SUM(CASE WHEN v.outcome = 'booked' THEN 1 ELSE 0 END) AS bookings,
ROUND(100 * SUM(CASE WHEN v.outcome = 'booked' THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_pct
FROM Broker b
JOIN BrokerVisit v ON v.broker_id = b.broker_id
GROUP BY b.broker_id, b.broker_name
HAVING COUNT(*) >= 3
ORDER BY conversion_pct DESC, b.broker_nameAnother way
SELECT b.broker_name, t.visits, t.bookings, ROUND(100 * t.bookings / t.visits, 1) AS conversion_pct FROM Broker b JOIN (SELECT broker_id, COUNT(*) AS visits, COUNT(CASE WHEN outcome = 'booked' THEN 1 END) AS bookings FROM BrokerVisit GROUP BY broker_id) t ON t.broker_id = b.broker_id WHERE t.visits > 2 ORDER BY conversion_pct DESC, b.broker_nameAnother way
SELECT b.broker_name, COUNT(v.visit_id) AS visits, SUM(IF(v.outcome = 'booked', 1, 0)) AS bookings, ROUND(AVG(IF(v.outcome = 'booked', 100, 0)), 1) AS conversion_pct FROM Broker b LEFT JOIN BrokerVisit v ON v.broker_id = b.broker_id GROUP BY b.broker_name HAVING COUNT(v.visit_id) >= 3 ORDER BY 4 DESC, 1← Costliest Rental Listing in Each City · Vacancy Gaps Between Tenants of a Flat →