Shared Ride Logic
ASKED IN INTERVIEWUber
Problem Statement
Uber’s efficiency depends on "Batching" rides. Two trips are considered "Shareable" if they meet the following criteria:
- They occur in the same city.
- Their time windows (from pickup_time to dropoff_time) overlap for at least 5 minutes.
- Both trips were requested on the same calendar day.
Write a query to find all pairs of unique trip IDs that could have been shared.
Rules:
- Return the pairs such that trip_id_1 is always less than trip_id_2 to avoid duplicate records (e.g., return (1, 2) but not (2, 1)).
- The overlap is defined as the time between the latest of the two pickup times and the earliest of the two dropoff times.
- Output columns: trip_id_1, trip_id_2, city_id, overlap_minutes.
- Sort by overlap_minutes in descending order, then trip_id_1 in ascending order.
Table Schema:
- Trip_Requests: trip_id, rider_id, city_id, pickup_time, dropoff_time.
- Rider_Profiles: rider_id, rating, is_prime (contextual data).
- City_Configs: city_id, city_name, timezone.
Tests your understanding of
Self Join, Interval Overlap, Spatial Logic and Filtering
Input Tables
Trip_Requests
| trip_id(INTEGER) | rider_id(INTEGER) | city_id(INTEGER) | pickup_time(TIMESTAMP) | dropoff_time(TIMESTAMP) |
|---|---|---|---|---|
| 101 | 1 | 1 | 2026-02-14 08:00:00 | 2026-02-14 08:30:00 |
| 102 | 2 | 1 | 2026-02-14 08:10:00 | 2026-02-14 08:40:00 |
| 103 | 3 | 1 | 2026-02-14 09:00:00 | 2026-02-14 09:15:00 |
| 104 | 4 | 2 | 2026-02-14 08:05:00 | 2026-02-14 08:25:00 |
| 105 | 5 | 1 | 2026-02-14 08:20:00 | 2026-02-14 08:50:00 |
Rider_Profiles
| rider_id(INTEGER) | is_prime(BOOLEAN) |
|---|---|
| 1 | true |
City_Configs
| city_id(INTEGER) | city_name(VARCHAR) |
|---|---|
| 1 | San Francisco |
Expected Output
| trip_id_1(INTEGER) | trip_id_2(INTEGER) | city_id(INTEGER) | overlap_minutes(INTEGER) |
|---|---|---|---|
| 101 | 102 | 1 | 20 |
| 102 | 105 | 1 | 20 |
| 101 | 105 | 1 | 10 |
Tags
HardASKED IN INTERVIEWSelf JoinInterval OverlapSpatial LogicFiltering
35-40 min
22%