Compute ETA shift and conversion uplift
Company: Uber
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Use PostgreSQL (SQL) and brief Python pseudocode. Assume 'today' is 2025-09-01.
Schema:
- trips(trip_id BIGINT, request_ts TIMESTAMP, city_id INT, rider_id BIGINT, driver_id BIGINT, shown_eta_sec INT, pickup_eta_sec INT, is_completed BOOLEAN, is_canceled BOOLEAN, surge_multiplier NUMERIC, is_treatment BOOLEAN, experiment_id INT)
- riders(rider_id BIGINT, signup_dt DATE, city_id INT)
- city_dim(city_id INT, city_name TEXT, tier TEXT)
- incentives(rider_id BIGINT, offer_start_dt DATE, offer_end_dt DATE, percent_off INT, cap_usd NUMERIC)
- day_weather(city_id INT, dt DATE, precip_mm NUMERIC, temp_c NUMERIC)
Small ASCII samples:
trips
trip_id | request_ts | city_id | rider_id | driver_id | shown_eta_sec | pickup_eta_sec | is_completed | is_canceled | surge_multiplier | is_treatment | experiment_id
1 | 2025-08-01 08:01:00 | 1 | 101 | 9001 | 420 | 480 | t | f | 1.0 | t | 42
2 | 2025-08-01 08:03:00 | 1 | 102 | 9002 | 360 | 360 | f | t | 1.2 | f | 42
3 | 2025-08-15 18:30:00 | 2 | 103 | 9003 | 300 | 420 | t | f | 1.1 | f | 42
4 | 2025-08-20 09:00:00 | 1 | 101 | 9001 | 330 | 360 | t | f | 1.0 | t | 42
5 | 2025-08-28 22:10:00 | 2 | 104 | 9004 | 600 | 660 | f | t | 1.5 | t | 42
riders
rider_id | signup_dt | city_id
101 | 2025-05-01 | 1
102 | 2025-01-15 | 1
103 | 2025-06-20 | 2
104 | 2025-03-10 | 2
city_dim
city_id | city_name | tier
1 | Alpha | T1
2 | Beta | T2
incentives
rider_id | offer_start_dt | offer_end_dt | percent_off | cap_usd
101 | 2025-08-15 | 2025-08-31 | 20 | 10
Tasks:
1) SQL: For experiment_id=42, compute for each city_id and calendar date the 7-day rolling median of shown_eta_sec and the request→trip conversion rate (completed/requests) from 2025-08-01 to 2025-09-01. Include days with zero requests (show conversion as NULL) by generating a date spine. Restrict to riders with signup_dt < 2025-07-01. Use percentile_cont(0.5) OVER a 7-day window for the median; clearly handle time zones by truncating request_ts at UTC midnight.
2) SQL: At city level, estimate a difference‑in‑differences conversion uplift between treated (is_treatment=true) and control for post=2025-08-15..2025-09-01 vs pre=2025-08-01..2025-08-14. Output: city_id, pre_treat_conv, pre_ctrl_conv, post_treat_conv, post_ctrl_conv, did_uplift = (post_treat_conv - pre_treat_conv) - (post_ctrl_conv - pre_ctrl_conv). Ensure riders with mixed treatment exposure are counted per their trip‑level exposure.
3) Python (pseudocode ok): Compute CUPED‑adjusted conversion at the rider_id level with X = pre‑period conversion and Y = post‑period conversion; estimate theta = cov(Y, X)/var(X), compute Y_cuped = Y - theta*(X - E[X]). Then estimate adjusted uplift between treated and control, with its standard error via cluster‑robust SEs at rider_id or city_id.
Overview: This question evaluates proficiency in SQL and Python for time‑series data manipulation and experimental analysis, covering window functions (7‑day percentile_cont medians), date spines and timezone‑aware timestamp truncation, conversion-rate aggregation, difference‑in‑differences uplift estimation, and CUPED adjustment with cluster‑robust standard errors. It is commonly asked to assess practical data engineering and applied statistics skills in the Data Manipulation (SQL/Python) domain, testing applied implementation and causal inference ability rather than only conceptual understanding.
Read the full Uber Data Scientist interview experience this question came from
7-Day Rolling ETA Median and Daily Conversion with Date Spine
Using **PostgreSQL**, you are analyzing an A/B experiment (`experiment_id = 42`) on rider-facing ETA estimates. For each `city_id` and each calendar date from **2025-08-01 to 2025-09-01 (inclusive)**, compute two daily metrics:
1. **7-day rolling median of `shown_eta_sec`** (in seconds) ending on that date, and
2. The **same-day request-to-trip conversion rate** (completed trips / total requests on that date).
### Input tables
- **`trips`** - one row per ride request. Relevant columns: `trip_id`, `request_ts` (TIMESTAMP, treat as UTC), `city_id`, `rider_id`, `shown_eta_sec` (INT seconds), `is_completed` (BOOLEAN), `experiment_id` (INT).
- **`riders`** - one row per rider. Relevant columns: `rider_id`, `signup_dt` (DATE).
### Rules
- Consider only rows with `experiment_id = 42`.
- Derive `request_date` as `date_trunc('day', request_ts AT TIME ZONE 'UTC')::date`.
- Restrict to **eligible riders** whose `signup_dt < 2025-07-01` (join `trips` to `riders` on `rider_id`).
- Restrict the analysis window to requests whose `request_date` is between 2025-08-01 and 2025-09-01 (inclusive).
- **Rolling median:** for each date that actually has eligible trips for a city, the 7-day rolling median is the `percentile_cont(0.5)` (continuous median) of `shown_eta_sec` over all eligible trips of that city whose `request_date` falls in the 7-calendar-day window `[request_date - 6 days, request_date]`.
- **Conversion:** for each date that has eligible trips, `daily_conversion = (# completed trips) / (total requests)` on that exact date.
- **Date spine:** every `city_id` that has any eligible trips in `experiment_id = 42` must appear once for **every** date from 2025-08-01 to 2025-09-01, even on days with zero requests. On a day with **zero requests** for a city, both `median_shown_eta_7d_sec` and `daily_conversion` must be **NULL**.
### Required output
One row per (`city_id`, date), with columns in this order:
- `city_id`
- `request_date` (DATE)
- `median_shown_eta_7d_sec` - the 7-day rolling median of `shown_eta_sec`, or NULL on zero-request days
- `daily_conversion` - same-day completed/total requests, or NULL on zero-request days
**Sort the result by `city_id`, then `request_date` ascending.**
Tables
trips(trip_id BIGINT, request_ts TIMESTAMP, city_id INT, rider_id BIGINT, driver_id BIGINT, shown_eta_sec INT, pickup_eta_sec INT, is_completed BOOLEAN, is_canceled BOOLEAN, surge_multiplier NUMERIC, is_treatment BOOLEAN, experiment_id INT)
riders(rider_id BIGINT, signup_dt DATE, city_id INT)
Hints
- percentile_cont is an ordered-set aggregate - PostgreSQL does NOT permit it as a window function (... OVER (...)). Compute the rolling median with a correlated subquery (or LATERAL) over the 7-day window instead.
- Build a date spine with CROSS JOIN generate_series('2025-08-01','2025-09-01', INTERVAL '1 day') against each experiment city, then LEFT JOIN the metrics so zero-request days stay NULL.
City-Level Difference-in-Differences Conversion Uplift
Using the same trips and riders tables, compute a city-level difference-in-differences (DiD) estimate of conversion uplift between treated trips (is_treatment = TRUE) and control trips (is_treatment = FALSE) for experiment_id = 42.
Define periods:
- Pre-period: 2025-08-01 to 2025-08-14 (inclusive)
- Post-period: 2025-08-15 to 2025-09-01 (inclusive)
Details:
- Treat request_ts as UTC and truncate to calendar dates via date_trunc('day', request_ts AT TIME ZONE 'UTC')::date.
- Restrict to riders with signup_dt < 2025-07-01.
- Conversion is defined as completed trips / total requests.
- For each city_id, compute:
- pre_treat_conv: average conversion rate for treated trips in the pre-period.
- pre_ctrl_conv: average conversion rate for control trips in the pre-period.
- post_treat_conv: average conversion rate for treated trips in the post-period.
- post_ctrl_conv: average conversion rate for control trips in the post-period.
- Riders who have both treated and control trips should contribute to each group according to their trip-level exposure (i.e., aggregate at the trip level, not rider level).
- Then compute the DiD uplift as:
did_uplift = (post_treat_conv - pre_treat_conv) - (post_ctrl_conv - pre_ctrl_conv).
Output columns:
- city_id
- pre_treat_conv
- pre_ctrl_conv
- post_treat_conv
- post_ctrl_conv
- did_uplift
Tables
trips(trip_id BIGINT, request_ts TIMESTAMP, city_id INT, rider_id BIGINT, driver_id BIGINT, shown_eta_sec INT, pickup_eta_sec INT, is_completed BOOLEAN, is_canceled BOOLEAN, surge_multiplier NUMERIC, is_treatment BOOLEAN, experiment_id INT)
riders(rider_id BIGINT, signup_dt DATE, city_id INT)
Hints
- Start by labeling each trip as pre or post based on the truncated request_date, and as treatment or control using is_treatment.
- Aggregate conversion rates by city_id, period, and is_treatment, then pivot these into columns and compute the DiD formula using simple arithmetic.