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
| Column | Type |
|---|---|
| request_id | int |
| rider_id | int |
| requested_at | datetime |
| status | enum(completed, cancelled, no_driver) |
| cancel_reason | enum(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_id | rider_id | requested_at | status | cancel_reason |
|---|---|---|---|---|
| 1 | 501 | 2024-05-10 08:01:00 | completed | NULL |
| 2 | 502 | 2024-05-10 08:03:00 | cancelled | rider_long_eta |
| 3 | 503 | 2024-05-10 08:05:00 | cancelled | driver_far_away |
| 4 | 501 | 2024-05-10 08:09:00 | no_driver | NULL |
| 5 | 504 | 2024-05-10 08:15:00 | completed | NULL |
| 6 | 505 | 2024-05-10 08:20:00 | cancelled | rider_changed_plans |
| 7 | 506 | 2024-05-10 08:22:00 | completed | NULL |
Output
| outcome | requests |
|---|---|
| Completed | 3 |
| Rider cancelled | 2 |
| Driver cancelled | 1 |
| No driver found | 1 |
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, outcomeAnother 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, outcomeAnother 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 →