Broker Site-Visit Conversion Rate

Medium
357.

Broker Site-Visit Conversion Rate

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

ColumnType
broker_idint
broker_namevarchar
rera_novarchar

Primary key: broker_id.

Registered channel partners; broker names are unique.

Table: BrokerVisit

ColumnTypeValues
visit_idint
broker_idint
visit_datedate
outcomeenumbooked, interested, not_interested, no_show

Primary key: visit_id.

One row per site visit a broker brought; broker_id is always in Broker.

Example

The rows must come back in the order shown.

Broker

broker_idbroker_namerera_no
1Sunil RealtyP52100011
2Kumar EstatesP52100023
3Prime HomesP52100037
4Nest BrokersP52100041

BrokerVisit

visit_idbroker_idvisit_dateoutcome
112025-02-01booked
212025-02-03interested
312025-02-08no_show
412025-02-09booked
522025-02-02booked
622025-02-05booked
732025-02-04not_interested
832025-02-06interested
932025-02-10no_show
1022025-02-11not_interested
2 more rows

Output

broker_namevisitsbookingsconversion_pct
Kumar Estates5360
Sunil Realty4250
Prime Homes300
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.

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

ColumnType
broker_idint
broker_namevarchar
rera_novarchar

Primary key: broker_id.

Registered channel partners; broker names are unique.

Table: BrokerVisit

ColumnType
visit_idint
broker_idint
visit_datedate
outcomeenum(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_idbroker_namerera_no
1Sunil RealtyP52100011
2Kumar EstatesP52100023
3Prime HomesP52100037
4Nest BrokersP52100041

BrokerVisit

visit_idbroker_idvisit_dateoutcome
112025-02-01booked
212025-02-03interested
312025-02-08no_show
412025-02-09booked
522025-02-02booked
622025-02-05booked
732025-02-04not_interested
832025-02-06interested
932025-02-10no_show
1022025-02-11not_interested
1122025-02-12booked
1222025-02-14interested

Output

broker_namevisitsbookingsconversion_pct
Kumar Estates5360
Sunil Realty4250
Prime Homes300

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_name

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

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