Uber: UberX Share Requests Matched With a Co-Rider
UberX Share puts riders going the same way in one car. Each request is assigned a shared_trip_id when it is dispatched; a request is matched only if at…
- Difficulty: Medium
- Topics: Subqueries, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #989
Problem statement
UberX Share puts riders going the same way in one car. Each request is assigned a shared_trip_id when it is dispatched; a request is matched only if at least one other request was assigned the same shared trip. A request with a NULL shared_trip_id was never dispatched and is unmatched.
For each city return city, requests (all Share requests), matched and match_pct (matched as a percentage of requests, rounded to 2 decimals). Order by match_pct descending, 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: ShareRequest
| Column | Type |
|---|---|
| request_id | int |
| rider_id | int |
| city | varchar |
| requested_at | datetime |
| shared_trip_id | int |
Primary key: request_id.
One row per UberX Share request. All requests on one shared trip are in the same city.
Examples
Example 1
ShareRequest
| request_id | rider_id | city | requested_at | shared_trip_id |
|---|---|---|---|---|
| 1 | 101 | Bengaluru | 2024-09-02 08:01:00 | 900 |
| 2 | 102 | Bengaluru | 2024-09-02 08:03:00 | 900 |
| 3 | 103 | Bengaluru | 2024-09-02 08:10:00 | 901 |
| 4 | 104 | Bengaluru | 2024-09-02 08:12:00 | NULL |
| 5 | 105 | Pune | 2024-09-02 09:00:00 | 902 |
| 6 | 106 | Pune | 2024-09-02 09:02:00 | 902 |
| 7 | 107 | Pune | 2024-09-02 09:04:00 | 902 |
| 8 | 108 | Pune | 2024-09-02 09:30:00 | NULL |
| 9 | 109 | Delhi | 2024-09-02 10:00:00 | NULL |
Output
| city | requests | matched | match_pct |
|---|---|---|---|
| Pune | 4 | 3 | 75 |
| Bengaluru | 4 | 2 | 50 |
| Delhi | 1 | 0 | 0 |
How to solve Uber: UberX Share Requests Matched With a Co-Rider
"Matched" is a property of a request that depends on other rows: request 1 is matched because request 2 has the same shared_trip_id. So the solution first measures every shared trip — COUNT(*) per shared_trip_id in a CTE — and then labels each request by the size of its trip.
Undispatched requests deserve care. If they were grouped, all the NULL shared_trip_ids of a city would form one group and look like a crowded shared ride. The CTE excludes them, and the LEFT JOIN cannot match them anyway (NULL equals nothing), so they keep a NULL size and fall into the ELSE 0 branch. Request 3, alone on trip 901, has size 1 and is unmatched too.
Per city, COUNT(*) counts every request and the conditional sum the matched ones; the percentage is rounded to two places. The alternative computes the trip size with a window, COUNT(shared_trip_id) OVER (PARTITION BY shared_trip_id) — counting the column rather than * makes it 0 for the NULL partition. Both are linear after the grouping.
Reference solution (MySQL)
WITH trip_size AS (
SELECT shared_trip_id, COUNT(*) AS riders
FROM ShareRequest
WHERE shared_trip_id IS NOT NULL
GROUP BY shared_trip_id
)
SELECT r.city,
COUNT(*) AS requests,
SUM(CASE WHEN s.riders >= 2 THEN 1 ELSE 0 END) AS matched,
ROUND(100 * SUM(CASE WHEN s.riders >= 2 THEN 1 ELSE 0 END) / COUNT(*), 2) AS match_pct
FROM ShareRequest r
LEFT JOIN trip_size s ON s.shared_trip_id = r.shared_trip_id
GROUP BY r.city
ORDER BY match_pct DESC, r.cityAnother way
SELECT city, COUNT(*) AS requests, SUM(is_matched) AS matched, ROUND(100 * SUM(is_matched) / COUNT(*), 2) AS match_pct
FROM (SELECT city, IF(COUNT(shared_trip_id) OVER (PARTITION BY shared_trip_id) >= 2, 1, 0) AS is_matched FROM ShareRequest) x
GROUP BY city
ORDER BY match_pct DESC, city← Uber: Uber Pro Leaderboard Around Driver 107 in Pune · Uber: Back-to-Back Trips Starting in the Last Drop Zone →