Uber: Pickup ETA Accuracy by City — SQL Dates Problem
When a driver accepts, the rider app quotes a pickup ETA in minutes. The maps team measures how often that promise is kept, city by city.
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #983
Problem statement
When a driver accepts, the rider app quotes a pickup ETA in minutes. The maps team measures how often that promise is kept, city by city.
A pickup is on time when the driver arrived within the quoted ETA plus a 2-minute grace, measured in seconds from accepted_at to arrived_at (arriving exactly at the edge is on time). It is badly late when it took more than the quoted ETA plus 5 minutes. Ignore requests where the driver never arrived (arrived_at NULL).
For each city return city, pickups, on_time_pct (percentage on time, rounded to 2 decimals) and badly_late (a count). Order by on_time_pct ascending, then city.
A practice scenario set at Uber: the tables and data are invented, and this is not a question from, or endorsed by, Uber.
Tables
Table: Pickup
| Column | Type |
|---|---|
| trip_id | int |
| city | varchar |
| accepted_at | datetime |
| quoted_eta_min | int |
| arrived_at | datetime |
Primary key: trip_id.
One row per accepted request. arrived_at is NULL when the trip was cancelled before the driver arrived.
Examples
Example 1
Pickup
| trip_id | city | accepted_at | quoted_eta_min | arrived_at |
|---|---|---|---|---|
| 1 | Bengaluru | 2024-07-01 09:00:00 | 5 | 2024-07-01 09:07:00 |
| 2 | Bengaluru | 2024-07-01 09:10:00 | 4 | 2024-07-01 09:16:01 |
| 3 | Bengaluru | 2024-07-01 09:20:00 | 3 | 2024-07-01 09:30:00 |
| 4 | Bengaluru | 2024-07-01 09:40:00 | 6 | NULL |
| 5 | Mumbai | 2024-07-01 10:00:00 | 8 | 2024-07-01 10:05:00 |
| 6 | Mumbai | 2024-07-01 10:10:00 | 5 | 2024-07-01 10:17:30 |
Output
| city | pickups | on_time_pct | badly_late |
|---|---|---|---|
| Bengaluru | 3 | 33.33 | 1 |
| Mumbai | 2 | 50 | 0 |
How to solve Uber: Pickup ETA Accuracy by City
Each pickup is reduced to one number, its actual wait in seconds, TIMESTAMPDIFF(SECOND, accepted_at, arrived_at). Working in seconds avoids a trap: TIMESTAMPDIFF(MINUTE, …) truncates, so a driver 7 minutes 59 seconds out would look like 7 minutes. The quoted ETA is converted to seconds too, so the two thresholds are (quoted_eta_min + 2) * 60 and (quoted_eta_min + 5) * 60.
Requests where the driver never arrived have no wait at all; they are removed in WHERE so they count neither as pickups nor in the percentage. Grouping by city, two conditional sums do the labelling: one counts waits at or under the on-time edge (inclusive — trip 1 arrived exactly at 5 + 2 minutes), the other waits strictly over the badly-late edge. The on-time count times 100 divided by COUNT(*), rounded, is the percentage.
The alternative first computes each pickup's lateness against the quote in a derived table, which makes the thresholds the constants 120 and 300 seconds. Sorting by the percentage with the city as a tie-breaker gives a fixed order. One scan, one aggregation.
Reference solution (MySQL)
SELECT city,
COUNT(*) AS pickups,
ROUND(100 * SUM(CASE WHEN TIMESTAMPDIFF(SECOND, accepted_at, arrived_at) <= (quoted_eta_min + 2) * 60 THEN 1 ELSE 0 END) / COUNT(*), 2) AS on_time_pct,
SUM(CASE WHEN TIMESTAMPDIFF(SECOND, accepted_at, arrived_at) > (quoted_eta_min + 5) * 60 THEN 1 ELSE 0 END) AS badly_late
FROM Pickup
WHERE arrived_at IS NOT NULL
GROUP BY city
ORDER BY on_time_pct, cityAnother way
SELECT city, COUNT(*) AS pickups,
ROUND(100 * SUM(IF(late_s <= 120, 1, 0)) / COUNT(*), 2) AS on_time_pct,
SUM(IF(late_s > 300, 1, 0)) AS badly_late
FROM (SELECT city, TIMESTAMPDIFF(SECOND, accepted_at, arrived_at) - quoted_eta_min * 60 AS late_s
FROM Pickup WHERE arrived_at IS NOT NULL) p
GROUP BY city
ORDER BY on_time_pct, city← Uber: Uber Eats Orders Handed to Another Courier · Uber: Drivers Gone Quiet Before the Festive Rush →