Quick Overview

This question evaluates SQL and Pandas data manipulation competencies, including joins, time-based filtering, handling NULLs, aggregations, rate and ratio calculations, and windowed comparisons over grouped city-day data.

Write SQL and Pandas for Uber Trips

Company: Uber

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Assume 'today' = 2025-09-01. You are given the following schema and small ASCII samples. Tables - riders(rider_id, name, signup_date) - drivers(driver_id, name, car_year) - trips(trip_id, rider_id, driver_id, request_time, pickup_time, dropoff_time, status, city, surge_multiplier, distance_km, fare_usd) • status ∈ {'completed','cancelled_by_rider','cancelled_by_driver','driver_no_show'} - payments(trip_id, amount_usd, method, card_hash, charge_time, success) - devices(user_type, user_id, device_id) where user_type ∈ {'rider','driver'} Samples (minimal, not exhaustive) riders rider_id | name | signup_date 1 | Alice | 2025-08-20 2 | Bob | 2025-08-28 3 | Chen | 2025-08-30 4 | Deepa | 2025-07-15 drivers driver_id | name | car_year 10 | Diego | 2018 11 | Elena | 2021 12 | Farid | 2015 trips trip_id | rider_id | driver_id | request_time | pickup_time | dropoff_time | status | city | surge_multiplier | distance_km | fare_usd 1 | 1 | 10 | 2025-08-25 19:55 | 2025-08-25 20:05 | 2025-08-25 20:25 | completed | SF | 1.8 | 8.0 | 24.5 2 | 2 | 11 | 2025-08-26 14:10 | 2025-08-26 14:15 | 2025-08-26 14:30 | completed | SF | 1.0 | 5.0 | 12.0 3 | 2 | 11 | 2025-08-26 20:05 | 2025-08-26 20:30 | NULL | cancelled_by_driver | SF | 2.0 | 0.0 | 0.0 4 | 3 | 12 | 2025-08-27 02:30 | NULL | NULL | cancelled_by_rider | NYC | 1.0 | 0.0 | 0.0 5 | 3 | 10 | 2025-08-30 20:15 | 2025-08-30 20:25 | 2025-08-30 20:45 | completed | NYC | 1.7 | 6.5 | 18.0 6 | 1 | 12 | 2025-08-31 14:05 | 2025-08-31 14:10 | 2025-08-31 14:30 | completed | SF | 1.0 | 7.0 | 16.0 7 | 4 | 10 | 2025-08-31 20:05 | 2025-08-31 20:20 | 2025-08-31 20:40 | completed | SF | 1.6 | 9.0 | 22.0 8 | 4 | 11 | 2025-09-01 02:10 | NULL | NULL | driver_no_show | SF | 1.0 | 0.0 | 0.0 payments trip_id | amount_usd | method | card_hash | charge_time | success 1 | 24.5 | card | abc123 | 2025-08-25 20:26 | true 2 | 12.0 | card | xyz777 | 2025-08-26 14:31 | true 3 | 0.0 | card | xyz777 | 2025-08-26 20:31 | false 4 | 0.0 | card | qwe555 | 2025-08-27 02:32 | false 5 | 18.0 | card | abc123 | 2025-08-30 20:46 | true 6 | 16.0 | card | abc123 | 2025-08-31 14:31 | true 7 | 22.0 | cash | NULL | 2025-08-31 20:41 | false 8 | 0.0 | card | xyz777 | 2025-09-01 02:12 | false devices user_type | user_id | device_id rider | 1 | devA rider | 2 | devB rider | 3 | devC rider | 4 | devB driver | 10 | devX driver | 11 | devY driver | 12 | devZ Write SQL answers for A–D and a Pandas answer for E: A) For the last 7 days (2025-08-26 to 2025-09-01 inclusive), compute per city: total requests, completed trips, and completion rate = completed / requests. Count a request if a row exists in trips (any status). Order by completion rate ascending. Handle NULL times robustly and ensure date filtering uses request_time in UTC. B) For each driver, over the last 7 days, compute the ratio: avg surge during 20:00–21:59 divided by avg surge during 14:00–15:59 on their completed trips, within the same city-day buckets. Return drivers where both windows have at least 1 completed trip and the ratio > 1.5. Include driver_id, city, counts per window, both averages, and the ratio. C) Over the last 7 days, compute the median pickup wait (pickup_time - request_time) per city for completed trips, after excluding trips above the city-specific 95th percentile wait. Use window functions to compute the percentile cutoff and the median on the truncated set. D) Identify likely duplicate rider accounts in the last 30 days: output rider_id_a, rider_id_b (a<b), evidence_type ('device' or 'card'), evidence_value (device_id or card_hash), first_seen_time. A pair qualifies if the riders share either the same device_id in devices or the same payments.card_hash used on trips by different rider_ids. Exclude NULL evidence values. For 'card', join payments→trips to map card_hash to rider_id. E) Pandas: Given a DataFrame trips_df of trips, compute 7-day new-user retention by cohort for riders whose first completed trip date is between 2025-08-25 and 2025-08-31. A rider is retained if they have ≥1 additional completed trip with dropoff_time within 7 days (inclusive) after their first completed trip. Return a DataFrame with cohort_date, n_new, n_retained, retention_rate, sorted by cohort_date.

Overview: This question evaluates SQL and Pandas data manipulation competencies, including joins, time-based filtering, handling NULLs, aggregations, rate and ratio calculations, and windowed comparisons over grouped city-day data.

City-level completion rate over a 7-day window

For the 7-day window FROM 2025-05-26 TO 2025-06-01 (inclusive), using request_time (UTC), compute per city: (1) total ride requests, (2) total completed trips, and (3) completion_rate = completed_trips / total_requests. Count a request if a row exists in trips with any status. Handle NULL pickup/dropoff times robustly (they should not affect counts). Order the result by completion_rate ascending.

Tables

riders(rider_id INT, name VARCHAR(50), signup_date DATE)

drivers(driver_id INT, name VARCHAR(50), car_year INT)

trips(trip_id INT, rider_id INT, driver_id INT, request_time TIMESTAMP, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, status VARCHAR(32), city VARCHAR(50), surge_multiplier DECIMAL(3,1), distance_km DECIMAL(5,1), fare_usd DECIMAL(6,2))

payments(trip_id INT, amount_usd DECIMAL(6,2), method VARCHAR(20), card_hash VARCHAR(100), charge_time TIMESTAMP, success BOOLEAN)

devices(user_type VARCHAR(10), user_id INT, device_id VARCHAR(50))

Hints

  1. Filter trips by request_time between '2025-05-26' and '2025-06-01' (inclusive) and group by city.
  2. Compute completion_rate as completed_trips * 1.0 / total_requests to avoid integer division.

Driver surge ratio between evening and afternoon windows

For each driver, over the 7-day window FROM 2025-05-26 TO 2025-06-01 (inclusive), consider only completed trips. For each driver, city, and service_date = DATE(request_time), compute: - avg_surge_afternoon: average surge_multiplier for trips with request_time between 14:00 and 15:59. - avg_surge_evening: average surge_multiplier for trips with request_time between 20:00 and 21:59. Return rows where, within the same (driver_id, city, service_date) bucket, there is at least 1 completed trip in each window and the ratio avg_surge_evening / avg_surge_afternoon > 1.5. Output columns: driver_id, city, service_date, afternoon_trip_count, evening_trip_count, avg_surge_afternoon, avg_surge_evening, surge_ratio. Use request_time (UTC) for time-of-day bucketing.

Tables

riders(rider_id INT, name VARCHAR(50), signup_date DATE)

drivers(driver_id INT, name VARCHAR(50), car_year INT)

trips(trip_id INT, rider_id INT, driver_id INT, request_time TIMESTAMP, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, status VARCHAR(32), city VARCHAR(50), surge_multiplier DECIMAL(3,1), distance_km DECIMAL(5,1), fare_usd DECIMAL(6,2))

Hints

  1. Bucket completed trips into afternoon and evening using EXTRACT(HOUR FROM request_time) and conditional aggregates.
  2. Group by driver_id, city, and DATE(request_time), then filter to groups with counts in both windows and a surge ratio above 1.5.

Median pickup wait per city excluding 95th percentile outliers

## Median pickup wait per city (excluding 95th-percentile outliers) You are analyzing rider pickup waits from the **`trips`** table. Consider only **completed** trips (`status = 'completed'`) whose `request_time` falls within the **7-day window from `2025-05-26` to `2025-06-01` inclusive** (i.e. `request_time >= '2025-05-26 00:00:00'` and `request_time < '2025-06-02 00:00:00'`). Ignore any trip with a NULL `pickup_time`. For each qualifying trip, define the **pickup wait time in minutes** as `(pickup_time - request_time)` expressed in minutes. Then, **per city**: 1. Compute the **95th percentile** of the wait time using a continuous percentile (`PERCENTILE_CONT(0.95)`). 2. **Exclude** any trip whose wait time is **strictly greater than** that city's 95th-percentile cutoff (i.e. keep trips where `wait_minutes <= p95`). 3. On the remaining (truncated) set, compute the **median** wait time using `PERCENTILE_CONT(0.5)`. ### Output Return one row per city with columns: - **`city`** — the city name - **`median_wait_minutes`** — the median pickup wait in minutes for that city after outlier removal, rounded to 2 decimal places Sort the result by **`city`** ascending.

Tables

riders(rider_id INT, name VARCHAR(50), signup_date DATE)

drivers(driver_id INT, name VARCHAR(50), car_year INT)

trips(trip_id INT, rider_id INT, driver_id INT, request_time TIMESTAMP, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, status VARCHAR(32), city VARCHAR(50), surge_multiplier DECIMAL(3,1), distance_km DECIMAL(5,1), fare_usd DECIMAL(6,2))

Hints

  1. Subtract the two timestamps and convert to minutes with EXTRACT(EPOCH FROM (pickup_time - request_time)) / 60.0.
  2. In PostgreSQL, PERCENTILE_CONT cannot be a window function (no OVER) — compute each city's p95 in a GROUP BY CTE and join it back to filter outliers.

Detecting likely duplicate rider accounts via shared devices or cards

Identify likely duplicate rider accounts in the last 30 days, defined as FROM 2025-05-03 TO 2025-06-01 (inclusive). A rider pair (rider_id_a, rider_id_b) with rider_id_a < rider_id_b qualifies if: 1) They share the same non-NULL device_id in the devices table (user_type = 'rider'), based on trips taken in that 30-day window; OR 2) They share the same non-NULL payments.card_hash, where card_hash is mapped to rider_id via payments.trip_id -> trips.trip_id, and the payment charge_time is in that 30-day window. For each qualifying pair and evidence source, output: rider_id_a, rider_id_b, evidence_type ('device' or 'card'), evidence_value (device_id or card_hash), and first_seen_time, defined as the earliest request_time (for device evidence) or charge_time (for card evidence) in the 30-day window where this shared identifier is observed for either rider. Return one row per pair per evidence_type. Return first_seen_time formatted as YYYY-MM-DD HH24:MI:SS.

Tables

riders(rider_id INT, name VARCHAR(50), signup_date DATE)

drivers(driver_id INT, name VARCHAR(50), car_year INT)

trips(trip_id INT, rider_id INT, driver_id INT, request_time TIMESTAMP, pickup_time TIMESTAMP, dropoff_time TIMESTAMP, status VARCHAR(32), city VARCHAR(50), surge_multiplier DECIMAL(3,1), distance_km DECIMAL(5,1), fare_usd DECIMAL(6,2))

payments(trip_id INT, amount_usd DECIMAL(6,2), method VARCHAR(20), card_hash VARCHAR(100), charge_time TIMESTAMP, success BOOLEAN)

devices(user_type VARCHAR(10), user_id INT, device_id VARCHAR(50))

Hints

  1. For devices, aggregate per rider and device to get the earliest request_time in the 30-day window, then self-join on device_id to form rider pairs.
  2. For cards, map payments to riders via trips, compute the earliest charge_time per rider and card_hash, self-join on card_hash to form pairs, and UNION these results with the device-based pairs.

Loading coding console...