Uber: Pickup ETA Accuracy by City

Medium
983.

Uber: Pickup ETA Accuracy by City

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

ColumnType
trip_idint
cityvarchar
accepted_atdatetime
quoted_eta_minint
arrived_atdatetime

Primary key: trip_id.

One row per accepted request. arrived_at is NULL when the trip was cancelled before the driver arrived.

Example

The rows must come back in the order shown.

Pickup

trip_idcityaccepted_atquoted_eta_minarrived_at
1Bengaluru2024-07-01 09:00:0052024-07-01 09:07:00
2Bengaluru2024-07-01 09:10:0042024-07-01 09:16:01
3Bengaluru2024-07-01 09:20:0032024-07-01 09:30:00
4Bengaluru2024-07-01 09:40:006NULL
5Mumbai2024-07-01 10:00:0082024-07-01 10:05:00
6Mumbai2024-07-01 10:10:0052024-07-01 10:17:30

Output

citypickupson_time_pctbadly_late
Bengaluru333.331
Mumbai2500
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.

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

ColumnType
trip_idint
cityvarchar
accepted_atdatetime
quoted_eta_minint
arrived_atdatetime

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_idcityaccepted_atquoted_eta_minarrived_at
1Bengaluru2024-07-01 09:00:0052024-07-01 09:07:00
2Bengaluru2024-07-01 09:10:0042024-07-01 09:16:01
3Bengaluru2024-07-01 09:20:0032024-07-01 09:30:00
4Bengaluru2024-07-01 09:40:006NULL
5Mumbai2024-07-01 10:00:0082024-07-01 10:05:00
6Mumbai2024-07-01 10:10:0052024-07-01 10:17:30

Output

citypickupson_time_pctbadly_late
Bengaluru333.331
Mumbai2500

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, city

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