Ride Request Outcome Mix: Rider, Driver or No Driver

Easy
302.

Ride Request Outcome Mix: Rider, Driver or No Driver

Every ride request ends one of three ways: it becomes a trip (completed), nobody accepts it (no_driver), or someone cancels it (cancelled, with a cancel_reason). Reasons that start with rider_ were rider cancellations and reasons that start with driver_ were driver cancellations.

Label each request as 'Completed', 'No driver found', 'Rider cancelled' or 'Driver cancelled' and return outcome and requests (the count) for every label that occurs. Order by requests descending, then outcome ascending.

Tables

Table: RideRequest

ColumnTypeValues
request_idint
rider_idint
requested_atdatetime
statusenumcompleted, cancelled, no_driver
cancel_reasonenumrider_changed_plans, rider_long_eta, driver_far_away, driver_vehicle_issue

Primary key: request_id.

cancel_reason is set only when status is cancelled, and NULL otherwise.

Example

The rows must come back in the order shown.

RideRequest

request_idrider_idrequested_atstatuscancel_reason
15012024-05-10 08:01:00completedNULL
25022024-05-10 08:03:00cancelledrider_long_eta
35032024-05-10 08:05:00cancelleddriver_far_away
45012024-05-10 08:09:00no_driverNULL
55042024-05-10 08:15:00completedNULL
65052024-05-10 08:20:00cancelledrider_changed_plans
75062024-05-10 08:22:00completedNULL

Output

outcomerequests
Completed3
Rider cancelled2
Driver cancelled1
No driver found1
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.

Ride Request Outcome Mix: Rider, Driver or No Driver

Every ride request ends one of three ways: it becomes a trip (completed), nobody accepts it (no_driver), or someone cancels it (cancelled, with a…

  • Difficulty: Easy
  • Topics: Conditional Logic, Aggregation
  • Dialect: MySQL
  • Problem: #302

Problem statement

Every ride request ends one of three ways: it becomes a trip (completed), nobody accepts it (no_driver), or someone cancels it (cancelled, with a cancel_reason). Reasons that start with rider_ were rider cancellations and reasons that start with driver_ were driver cancellations.

Label each request as 'Completed', 'No driver found', 'Rider cancelled' or 'Driver cancelled' and return outcome and requests (the count) for every label that occurs. Order by requests descending, then outcome ascending.

Tables

Table: RideRequest

ColumnType
request_idint
rider_idint
requested_atdatetime
statusenum(completed, cancelled, no_driver)
cancel_reasonenum(rider_changed_plans, rider_long_eta, driver_far_away, driver_vehicle_issue)

Primary key: request_id.

cancel_reason is set only when status is cancelled, and NULL otherwise.

Examples

Example 1

RideRequest

request_idrider_idrequested_atstatuscancel_reason
15012024-05-10 08:01:00completedNULL
25022024-05-10 08:03:00cancelledrider_long_eta
35032024-05-10 08:05:00cancelleddriver_far_away
45012024-05-10 08:09:00no_driverNULL
55042024-05-10 08:15:00completedNULL
65052024-05-10 08:20:00cancelledrider_changed_plans
75062024-05-10 08:22:00completedNULL

Output

outcomerequests
Completed3
Rider cancelled2
Driver cancelled1
No driver found1

How to solve Ride Request Outcome Mix: Rider, Driver or No Driver

The output groups by a label that is not stored anywhere, so build it with a CASE expression and group by it. The branches run top to bottom and the first true one wins: test status = 'completed' and status = 'no_driver' first; any row left is a cancellation, and its cancel_reason decides who cancelled — a rider_ prefix means the rider, everything else (the driver_ reasons) the driver.

In a LIKE pattern the underscore is itself a wildcard (any one character), so 'rider_%' would not test for a literal underscore; 'rider%' is enough here because no driver reason starts with rider. LEFT(cancel_reason, 6) = 'rider_' avoids pattern syntax altogether.

MySQL lets you GROUP BY the select alias; repeating the whole CASE also works. Only labels that occur form groups, so a label with zero requests is simply absent. The UNION ALL alternative counts each label with conditional aggregation and must filter out zero counts to match. All versions are one or four scans of the table.

Reference solution (MySQL)

SELECT CASE
         WHEN status = 'completed' THEN 'Completed'
         WHEN status = 'no_driver' THEN 'No driver found'
         WHEN cancel_reason LIKE 'rider%' THEN 'Rider cancelled'
         ELSE 'Driver cancelled'
       END AS outcome,
       COUNT(*) AS requests
FROM RideRequest
GROUP BY outcome
ORDER BY requests DESC, outcome

Another way

SELECT outcome, COUNT(*) AS requests FROM (SELECT IF(status = 'completed', 'Completed', IF(status = 'no_driver', 'No driver found', IF(LEFT(cancel_reason, 6) = 'rider_', 'Rider cancelled', 'Driver cancelled'))) AS outcome FROM RideRequest) r GROUP BY outcome ORDER BY requests DESC, outcome

Another way

SELECT outcome, requests FROM (
  SELECT 'Completed' AS outcome, SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS requests FROM RideRequest
  UNION ALL SELECT 'No driver found', SUM(CASE WHEN status = 'no_driver' THEN 1 ELSE 0 END) FROM RideRequest
  UNION ALL SELECT 'Rider cancelled', SUM(CASE WHEN status = 'cancelled' AND cancel_reason LIKE 'rider%' THEN 1 ELSE 0 END) FROM RideRequest
  UNION ALL SELECT 'Driver cancelled', SUM(CASE WHEN status = 'cancelled' AND cancel_reason LIKE 'driver%' THEN 1 ELSE 0 END) FROM RideRequest
) x WHERE requests > 0 ORDER BY requests DESC, outcome

← Active Fleet Vehicles by Registration State · Late-Night Ride Requests for the May Safety Audit →