Uber: UberX Share Requests Matched With a Co-Rider

Medium
989.

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

ColumnType
request_idint
rider_idint
cityvarchar
requested_atdatetime
shared_trip_idint

Primary key: request_id.

One row per UberX Share request. All requests on one shared trip are in the same city.

Example

The rows must come back in the order shown.

ShareRequest

request_idrider_idcityrequested_atshared_trip_id
1101Bengaluru2024-09-02 08:01:00900
2102Bengaluru2024-09-02 08:03:00900
3103Bengaluru2024-09-02 08:10:00901
4104Bengaluru2024-09-02 08:12:00NULL
5105Pune2024-09-02 09:00:00902
6106Pune2024-09-02 09:02:00902
7107Pune2024-09-02 09:04:00902
8108Pune2024-09-02 09:30:00NULL
9109Delhi2024-09-02 10:00:00NULL

Output

cityrequestsmatchedmatch_pct
Pune4375
Bengaluru4250
Delhi100
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: 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

ColumnType
request_idint
rider_idint
cityvarchar
requested_atdatetime
shared_trip_idint

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_idrider_idcityrequested_atshared_trip_id
1101Bengaluru2024-09-02 08:01:00900
2102Bengaluru2024-09-02 08:03:00900
3103Bengaluru2024-09-02 08:10:00901
4104Bengaluru2024-09-02 08:12:00NULL
5105Pune2024-09-02 09:00:00902
6106Pune2024-09-02 09:02:00902
7107Pune2024-09-02 09:04:00902
8108Pune2024-09-02 09:30:00NULL
9109Delhi2024-09-02 10:00:00NULL

Output

cityrequestsmatchedmatch_pct
Pune4375
Bengaluru4250
Delhi100

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.city

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