Uber: Rider Cancellations That Earn a Late-Cancel Fee
Riders may cancel for free for a short while after a driver accepts; after that the driver has already started driving over, so the rider is charged a…
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #974
Problem statement
Riders may cancel for free for a short while after a driver accepts; after that the driver has already started driving over, so the rider is charged a cancellation fee. The payments team wants the list of trips to charge.
A trip is charged when its status is rider_cancelled, a driver had accepted it (accepted_at is not NULL), and the rider cancelled more than 120 seconds after the acceptance. Return trip_id, rider_id and seconds_after_accept (whole seconds from accepted_at to cancelled_at), ordered by trip_id.
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: TripRequest
| Column | Type |
|---|---|
| trip_id | int |
| rider_id | int |
| requested_at | datetime |
| accepted_at | datetime |
| cancelled_at | datetime |
| status | enum(completed, rider_cancelled, driver_cancelled, no_driver) |
Primary key: trip_id.
One row per ride request. accepted_at is NULL when no driver accepted; cancelled_at is NULL unless the request was cancelled.
Examples
Example 1
TripRequest
| trip_id | rider_id | requested_at | accepted_at | cancelled_at | status |
|---|---|---|---|---|---|
| 101 | 11 | 2024-05-10 08:00:00 | 2024-05-10 08:00:40 | 2024-05-10 08:04:10 | rider_cancelled |
| 102 | 12 | 2024-05-10 08:05:00 | 2024-05-10 08:05:20 | 2024-05-10 08:07:20 | rider_cancelled |
| 103 | 13 | 2024-05-10 08:10:00 | NULL | 2024-05-10 08:16:00 | rider_cancelled |
| 104 | 11 | 2024-05-10 09:00:00 | 2024-05-10 09:01:00 | NULL | completed |
| 105 | 14 | 2024-05-10 09:30:00 | 2024-05-10 09:30:30 | 2024-05-10 09:40:00 | driver_cancelled |
| 106 | 15 | 2024-05-10 10:00:00 | 2024-05-10 10:00:15 | 2024-05-10 10:02:16 | rider_cancelled |
| 107 | 16 | 2024-05-10 10:20:00 | NULL | NULL | no_driver |
Output
| trip_id | rider_id | seconds_after_accept |
|---|---|---|
| 101 | 11 | 210 |
| 106 | 15 | 121 |
How to solve Uber: Rider Cancellations That Earn a Late-Cancel Fee
The fee rule is three conditions on one row, so the whole query is a filter. The status must be rider_cancelled; a driver had to accept first, because the fee pays for a driver who was already on the way; and the cancellation must come after the free window, measured from acceptance, not from the request.
TIMESTAMPDIFF(SECOND, accepted_at, cancelled_at) is the length of that wait. It appears in both the WHERE clause and the select list, where it becomes seconds_after_accept. The comparison is strict (> 120), so trip 102, cancelled exactly two minutes after acceptance, is not charged.
When accepted_at is NULL the difference is NULL and NULL > 120 is not true, so the explicit IS NOT NULL is belt and braces — the alternative relies on that NULL behaviour and compares cancelled_at with DATE_ADD(accepted_at, INTERVAL 2 MINUTE) instead. Both are one pass over the table.
Reference solution (MySQL)
SELECT trip_id, rider_id, TIMESTAMPDIFF(SECOND, accepted_at, cancelled_at) AS seconds_after_accept
FROM TripRequest
WHERE status = 'rider_cancelled'
AND accepted_at IS NOT NULL
AND TIMESTAMPDIFF(SECOND, accepted_at, cancelled_at) > 120
ORDER BY trip_idAnother way
SELECT trip_id, rider_id, TIMESTAMPDIFF(SECOND, accepted_at, cancelled_at) AS seconds_after_accept
FROM TripRequest
WHERE status = 'rider_cancelled' AND cancelled_at > DATE_ADD(accepted_at, INTERVAL 2 MINUTE)
ORDER BY trip_id← Uber: Uber One Trials Ending Without Auto-Renew · Uber: Freight Loads Booked Below the Posted Rate →