Uber: Rider Cancellations That Earn a Late-Cancel Fee

Easy
974.

Uber: Rider Cancellations That Earn a Late-Cancel Fee

EasyDatesBasics

MySQL

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

ColumnTypeValues
trip_idint
rider_idint
requested_atdatetime
accepted_atdatetime
cancelled_atdatetime
statusenumcompleted, 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.

Example

The rows must come back in the order shown.

TripRequest

trip_idrider_idrequested_ataccepted_atcancelled_atstatus
101112024-05-10 08:00:002024-05-10 08:00:402024-05-10 08:04:10rider_cancelled
102122024-05-10 08:05:002024-05-10 08:05:202024-05-10 08:07:20rider_cancelled
103132024-05-10 08:10:00NULL2024-05-10 08:16:00rider_cancelled
104112024-05-10 09:00:002024-05-10 09:01:00NULLcompleted
105142024-05-10 09:30:002024-05-10 09:30:302024-05-10 09:40:00driver_cancelled
106152024-05-10 10:00:002024-05-10 10:00:152024-05-10 10:02:16rider_cancelled
107162024-05-10 10:20:00NULLNULLno_driver

Output

trip_idrider_idseconds_after_accept
10111210
10615121
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: 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

ColumnType
trip_idint
rider_idint
requested_atdatetime
accepted_atdatetime
cancelled_atdatetime
statusenum(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_idrider_idrequested_ataccepted_atcancelled_atstatus
101112024-05-10 08:00:002024-05-10 08:00:402024-05-10 08:04:10rider_cancelled
102122024-05-10 08:05:002024-05-10 08:05:202024-05-10 08:07:20rider_cancelled
103132024-05-10 08:10:00NULL2024-05-10 08:16:00rider_cancelled
104112024-05-10 09:00:002024-05-10 09:01:00NULLcompleted
105142024-05-10 09:30:002024-05-10 09:30:302024-05-10 09:40:00driver_cancelled
106152024-05-10 10:00:002024-05-10 10:00:152024-05-10 10:02:16rider_cancelled
107162024-05-10 10:20:00NULLNULLno_driver

Output

trip_idrider_idseconds_after_accept
10111210
10615121

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_id

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