Dispatch Offer Acceptance Rate per Driver

Medium
306.

Dispatch Offer Acceptance Rate per Driver

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

ColumnTypeValues
offer_idint
driver_idint
offered_atdatetime
responseenumaccepted, rejected, timed_out

Primary key: offer_id.

One row per offer the dispatch engine sent to a driver.

Example

The rows must come back in the order shown.

DispatchOffer

offer_iddriver_idoffered_atresponse
1212024-07-01 08:00:00accepted
2212024-07-01 08:20:00rejected
3212024-07-01 09:05:00accepted
4212024-07-01 09:40:00timed_out
5212024-07-01 10:10:00accepted
6222024-07-01 08:02:00accepted
7222024-07-01 08:30:00accepted
8222024-07-01 09:00:00accepted
9232024-07-01 08:05:00timed_out
10232024-07-01 08:45:00accepted
4 more rows

Output

driver_idoffersacceptance_pct
21560
23650
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.

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

ColumnType
offer_idint
driver_idint
offered_atdatetime
responseenum(accepted, rejected, timed_out)

Primary key: offer_id.

One row per offer the dispatch engine sent to a driver.

Examples

Example 1

DispatchOffer

offer_iddriver_idoffered_atresponse
1212024-07-01 08:00:00accepted
2212024-07-01 08:20:00rejected
3212024-07-01 09:05:00accepted
4212024-07-01 09:40:00timed_out
5212024-07-01 10:10:00accepted
6222024-07-01 08:02:00accepted
7222024-07-01 08:30:00accepted
8222024-07-01 09:00:00accepted
9232024-07-01 08:05:00timed_out
10232024-07-01 08:45:00accepted
11232024-07-01 09:15:00rejected
12232024-07-01 09:50:00rejected
13232024-07-01 10:30:00accepted
14232024-07-01 11:00:00accepted

Output

driver_idoffersacceptance_pct
21560
23650

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_id

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

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