cswork / libraryOpen practice workspace

Coding questions · Uber

Write SQL and Pandas for Uber Trips

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.

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.

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.

Table riders

columntypeconstraints
rider_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
signup_dateDATENOT NULL

Sample data:

namerider_idsignup_date
Alice12025-05-20
Bob22025-05-28
Chen32025-05-30
Deepa42025-04-15

Table drivers

columntypeconstraints
driver_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
car_yearINTNOT NULL

Sample data:

car_yeardriver_idname
201810Diego
202111Elena
201512Farid

Table trips

columntypeconstraints
trip_idINTPRIMARY KEY
rider_idINTNOT NULL
driver_idINTNOT NULL
request_timeTIMESTAMPNOT NULL
pickup_timeTIMESTAMP
dropoff_timeTIMESTAMP
statusVARCHAR(32)NOT NULL
cityVARCHAR(50)NOT NULL
surge_multiplierDECIMAL(3,1)NOT NULL
distance_kmDECIMAL(5,1)NOT NULL
fare_usdDECIMAL(6,2)NOT NULL

Sample data:

citydistance_kmdriver_iddropoff_timefare_usdpickup_timerequest_timerider_idstatussurge_multipliertrip_id
SF8102025-05-25 20:25:0024.52025-05-25 20:05:002025-05-25 19:55:001completed1.81
SF5112025-05-26 14:30:00122025-05-26 14:15:002025-05-26 14:10:002completed12
SF011NULL02025-05-26 20:30:002025-05-26 20:05:002cancelled_by_driver23
NYC012NULL0NULL2025-05-27 02:30:003cancelled_by_rider14
NYC6.5102025-05-30 20:45:00182025-05-30 20:25:002025-05-30 20:15:003completed1.75
SF7102025-05-31 14:30:00162025-05-31 14:10:002025-05-31 14:05:001completed16
SF9102025-05-31 20:40:00222025-05-31 20:20:002025-05-31 20:05:004completed1.67
SF011NULL0NULL2025-06-01 02:10:004driver_no_show18

Table payments

columntypeconstraints
trip_idINTPRIMARY KEY
amount_usdDECIMAL(6,2)NOT NULL
methodVARCHAR(20)NOT NULL
card_hashVARCHAR(100)
charge_timeTIMESTAMPNOT NULL
successBOOLEANNOT NULL

Sample data:

amount_usdcard_hashcharge_timemethodsuccesstrip_id
24.5abc1232025-05-25 20:26:00cardtrue1
12xyz7772025-05-26 14:31:00cardtrue2
0xyz7772025-05-26 20:31:00cardfalse3
0qwe5552025-05-27 02:32:00cardfalse4
18abc1232025-05-30 20:46:00cardtrue5
16abc1232025-05-31 14:31:00cardtrue6
22NULL2025-05-31 20:41:00cashfalse7
0xyz7772025-06-01 02:12:00cardfalse8

Table devices

columntypeconstraints
user_typeVARCHAR(10)NOT NULL
user_idINTNOT NULL
device_idVARCHAR(50)NOT NULL

Sample data:

device_iduser_iduser_type
devA1rider
devB2rider
devC3rider
devB4rider
devX10driver
devY11driver
devZ12driver

Expected output

One row per city with total requests, total completed trips, and completion_rate over 2025-05-26 to 2025-06-01 based on request_time.

citycompleted_tripscompletion_ratetotal_requests
NYC10.52
SF30.65

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.

Table riders

columntypeconstraints
rider_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
signup_dateDATENOT NULL

Sample data:

namerider_idsignup_date
Alice12025-05-20
Bob22025-05-28
Chen32025-05-30
Deepa42025-04-15

Table drivers

columntypeconstraints
driver_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
car_yearINTNOT NULL

Sample data:

car_yeardriver_idname
201810Diego
202111Elena
201512Farid

Table trips

columntypeconstraints
trip_idINTPRIMARY KEY
rider_idINTNOT NULL
driver_idINTNOT NULL
request_timeTIMESTAMPNOT NULL
pickup_timeTIMESTAMP
dropoff_timeTIMESTAMP
statusVARCHAR(32)NOT NULL
cityVARCHAR(50)NOT NULL
surge_multiplierDECIMAL(3,1)NOT NULL
distance_kmDECIMAL(5,1)NOT NULL
fare_usdDECIMAL(6,2)NOT NULL

Sample data:

citydistance_kmdriver_iddropoff_timefare_usdpickup_timerequest_timerider_idstatussurge_multipliertrip_id
SF8102025-05-25 20:25:0024.52025-05-25 20:05:002025-05-25 19:55:001completed1.81
SF5112025-05-26 14:30:00122025-05-26 14:15:002025-05-26 14:10:002completed12
SF011NULL02025-05-26 20:30:002025-05-26 20:05:002cancelled_by_driver23
NYC012NULL0NULL2025-05-27 02:30:003cancelled_by_rider14
NYC6.5102025-05-30 20:45:00182025-05-30 20:25:002025-05-30 20:15:003completed1.75
SF7102025-05-31 14:30:00162025-05-31 14:10:002025-05-31 14:05:001completed16
SF9102025-05-31 20:40:00222025-05-31 20:20:002025-05-31 20:05:004completed1.67
SF011NULL0NULL2025-06-01 02:10:004driver_no_show18

Expected output

Rows for driver-city-service_date combinations in 2025-05-26 to 2025-06-01 where both afternoon and evening windows have at least one completed trip and the surge ratio > 1.5.

afternoon_trip_countavg_surge_afternoonavg_surge_eveningcitydriver_idevening_trip_countservice_datesurge_ratio
111.6SF1012025-05-311.6

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.

Table riders

columntypeconstraints
rider_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
signup_dateDATENOT NULL

Sample data:

namerider_idsignup_date
Alice12025-05-20
Bob22025-05-28
Chen32025-05-30
Deepa42025-04-15

Table drivers

columntypeconstraints
driver_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
car_yearINTNOT NULL

Sample data:

car_yeardriver_idname
201810Diego
202111Elena
201512Farid

Table trips

columntypeconstraints
trip_idINTPRIMARY KEY
rider_idINTNOT NULL
driver_idINTNOT NULL
request_timeTIMESTAMPNOT NULL
pickup_timeTIMESTAMP
dropoff_timeTIMESTAMP
statusVARCHAR(32)NOT NULL
cityVARCHAR(50)NOT NULL
surge_multiplierDECIMAL(3,1)NOT NULL
distance_kmDECIMAL(5,1)NOT NULL
fare_usdDECIMAL(6,2)NOT NULL

Sample data:

citydistance_kmdriver_iddropoff_timefare_usdpickup_timerequest_timerider_idstatussurge_multipliertrip_id
SF8102025-05-25 20:25:0024.52025-05-25 20:05:002025-05-25 19:55:001completed1.81
SF5112025-05-26 14:30:00122025-05-26 14:15:002025-05-26 14:10:002completed12
SF011NULL02025-05-26 20:30:002025-05-26 20:05:002cancelled_by_driver23
NYC012NULL0NULL2025-05-27 02:30:003cancelled_by_rider14
NYC6.5102025-05-30 20:45:00182025-05-30 20:25:002025-05-30 20:15:003completed1.75
NYC10112025-05-31 09:10:00302025-05-31 08:50:002025-05-31 08:45:003completed1.29
NYC14122025-06-01 10:10:00452025-06-01 09:40:002025-06-01 09:00:004completed2.510
SF7102025-05-31 14:30:00162025-05-31 14:10:002025-05-31 14:05:001completed16
SF9102025-05-31 20:40:00222025-05-31 20:20:002025-05-31 20:05:004completed1.67
SF011NULL0NULL2025-06-01 02:10:004driver_no_show18

Expected output

One row per city with the median pickup wait (minutes, rounded to 2dp) over completed trips in the 7-day window, after dropping waits above each city's 95th percentile; sorted by city.

citymedian_wait_minutes
NYC7.50
SF5.00

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.
  3. After dropping waits above the city p95, compute the median with PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY wait_minutes), grouped by city.

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.

Table riders

columntypeconstraints
rider_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
signup_dateDATENOT NULL

Sample data:

namerider_idsignup_date
Alice12025-05-20
Bob22025-05-28
Chen32025-05-30
Deepa42025-04-15

Table drivers

columntypeconstraints
driver_idINTPRIMARY KEY
nameVARCHAR(50)NOT NULL
car_yearINTNOT NULL

Sample data:

car_yeardriver_idname
201810Diego
202111Elena
201512Farid

Table trips

columntypeconstraints
trip_idINTPRIMARY KEY
rider_idINTNOT NULL
driver_idINTNOT NULL
request_timeTIMESTAMPNOT NULL
pickup_timeTIMESTAMP
dropoff_timeTIMESTAMP
statusVARCHAR(32)NOT NULL
cityVARCHAR(50)NOT NULL
surge_multiplierDECIMAL(3,1)NOT NULL
distance_kmDECIMAL(5,1)NOT NULL
fare_usdDECIMAL(6,2)NOT NULL

Sample data:

citydistance_kmdriver_iddropoff_timefare_usdpickup_timerequest_timerider_idstatussurge_multipliertrip_id
SF8102025-05-25 20:25:0024.52025-05-25 20:05:002025-05-25 19:55:001completed1.81
SF5112025-05-26 14:30:00122025-05-26 14:15:002025-05-26 14:10:002completed12
SF011NULL02025-05-26 20:30:002025-05-26 20:05:002cancelled_by_driver23
NYC012NULL0NULL2025-05-27 02:30:003cancelled_by_rider14
NYC6.5102025-05-30 20:45:00182025-05-30 20:25:002025-05-30 20:15:003completed1.75
SF7102025-05-31 14:30:00162025-05-31 14:10:002025-05-31 14:05:001completed16
SF9102025-05-31 20:40:00222025-05-31 20:20:002025-05-31 20:05:004completed1.67
SF011NULL0NULL2025-06-01 02:10:004driver_no_show18

Table payments

columntypeconstraints
trip_idINTPRIMARY KEY
amount_usdDECIMAL(6,2)NOT NULL
methodVARCHAR(20)NOT NULL
card_hashVARCHAR(100)
charge_timeTIMESTAMPNOT NULL
successBOOLEANNOT NULL

Sample data:

amount_usdcard_hashcharge_timemethodsuccesstrip_id
24.5abc1232025-05-25 20:26:00cardtrue1
12xyz7772025-05-26 14:31:00cardtrue2
0xyz7772025-05-26 20:31:00cardfalse3
0qwe5552025-05-27 02:32:00cardfalse4
18abc1232025-05-30 20:46:00cardtrue5
16abc1232025-05-31 14:31:00cardtrue6
22NULL2025-05-31 20:41:00cashfalse7
0xyz7772025-06-01 02:12:00cardfalse8

Table devices

columntypeconstraints
user_typeVARCHAR(10)NOT NULL
user_idINTNOT NULL
device_idVARCHAR(50)NOT NULL

Sample data:

device_iduser_iduser_type
devA1rider
devB2rider
devC3rider
devB4rider
devX10driver
devY11driver
devZ12driver

Expected output

Each row represents a pair of riders (rider_id_a < rider_id_b) that share either a device_id or card_hash within the last 30 days, with the earliest time this shared identifier is observed.

evidence_typeevidence_valuefirst_seen_timerider_id_arider_id_b
cardabc1232025-05-25 20:26:0013
cardxyz7772025-05-26 14:31:0024
devicedevB2025-05-26 14:10:0024

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.