Dispatch Offer Acceptance Rate per Driver — SQL Problem
When a rider books, the dispatch engine offers the trip to a nearby driver, who can accept it, reject it, or let it time out.
- Difficulty: Medium
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #306
Problem statement
When a rider books, the dispatch engine offers the trip to a nearby driver, who can accept it, reject it, or let it time out. Ops tracks each driver's acceptance rate, but only for drivers with at least 5 offers — fewer is too noisy.
Return driver_id, offers (the number of offers made to the driver) and acceptance_pct (accepted offers as a percentage of all offers, rounded to 2 decimals; rejected and timed-out offers both count as not accepted). Order by acceptance_pct descending, then driver_id ascending.
Tables
Table: DispatchOffer
| Column | Type |
|---|---|
| offer_id | int |
| driver_id | int |
| offered_at | datetime |
| response | enum(accepted, rejected, timed_out) |
Primary key: offer_id.
One row per offer the dispatch engine sent to a driver.
Examples
Example 1
DispatchOffer
| offer_id | driver_id | offered_at | response |
|---|---|---|---|
| 1 | 21 | 2024-07-01 08:00:00 | accepted |
| 2 | 21 | 2024-07-01 08:20:00 | rejected |
| 3 | 21 | 2024-07-01 09:05:00 | accepted |
| 4 | 21 | 2024-07-01 09:40:00 | timed_out |
| 5 | 21 | 2024-07-01 10:10:00 | accepted |
| 6 | 22 | 2024-07-01 08:02:00 | accepted |
| 7 | 22 | 2024-07-01 08:30:00 | accepted |
| 8 | 22 | 2024-07-01 09:00:00 | accepted |
| 9 | 23 | 2024-07-01 08:05:00 | timed_out |
| 10 | 23 | 2024-07-01 08:45:00 | accepted |
| 11 | 23 | 2024-07-01 09:15:00 | rejected |
| 12 | 23 | 2024-07-01 09:50:00 | rejected |
| 13 | 23 | 2024-07-01 10:30:00 | accepted |
| 14 | 23 | 2024-07-01 11:00:00 | accepted |
Output
| driver_id | offers | acceptance_pct |
|---|---|---|
| 21 | 5 | 60 |
| 23 | 6 | 50 |
How to solve Dispatch Offer Acceptance Rate per Driver
One group per driver gives the denominator directly: COUNT(*) is the number of offers. The numerator is a conditional count — SUM(CASE WHEN response = 'accepted' THEN 1 ELSE 0 END) adds 1 for each accepted offer and 0 for rejected and timed-out ones — so both numbers come out of the same pass over the table.
Multiply by 100 before dividing and round to two decimals as the statement asks; MySQL's / returns a decimal, so no integer truncation happens. The "at least 5 offers" rule filters groups, not rows, so it goes in HAVING COUNT(*) >= 5. A driver who never accepts still forms a group, and the CASE sum gives them 0 %.
Another neat form: the average of a 100-or-0 flag is the percentage, so AVG(IF(response = 'accepted', 100, 0)) does it in one function. Joining two pre-aggregated subqueries also works, but needs a LEFT JOIN and COALESCE so a driver with no accepted offer is kept. The single-pass versions cost one scan and a group-by.
Reference solution (MySQL)
SELECT driver_id,
COUNT(*) AS offers,
ROUND(100 * SUM(CASE WHEN response = 'accepted' THEN 1 ELSE 0 END) / COUNT(*), 2) AS acceptance_pct
FROM DispatchOffer
GROUP BY driver_id
HAVING COUNT(*) >= 5
ORDER BY acceptance_pct DESC, driver_idAnother way
SELECT driver_id, COUNT(offer_id) AS offers, ROUND(AVG(IF(response = 'accepted', 100, 0)), 2) AS acceptance_pct FROM DispatchOffer GROUP BY driver_id HAVING COUNT(offer_id) > 4 ORDER BY acceptance_pct DESC, driver_idAnother way
SELECT o.driver_id, o.offers, ROUND(100 * COALESCE(a.accepted, 0) / o.offers, 2) AS acceptance_pct FROM (SELECT driver_id, COUNT(*) AS offers FROM DispatchOffer GROUP BY driver_id) o LEFT JOIN (SELECT driver_id, COUNT(*) AS accepted FROM DispatchOffer WHERE response = 'accepted' GROUP BY driver_id) a ON a.driver_id = o.driver_id WHERE o.offers >= 5 ORDER BY acceptance_pct DESC, o.driver_id← Drivers Rated Above the Fleet Average · Couriers Who Missed the Delivery SLA on Over 20% of May Drops →