cswork / libraryOpen practice workspace

Coding questions · Uber

Compute ETA shift and conversion uplift

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.

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.

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.

Table trips

columntypeconstraints
trip_idBIGINTPRIMARY KEY
request_tsTIMESTAMPNOT NULL
city_idINTNOT NULL
rider_idBIGINTNOT NULL
driver_idBIGINT
shown_eta_secINT
pickup_eta_secINT
is_completedBOOLEAN
is_canceledBOOLEAN
surge_multiplierNUMERIC
is_treatmentBOOLEAN
experiment_idINT

Sample data:

city_iddriver_idexperiment_idis_canceledis_completedis_treatmentpickup_eta_secrequest_tsrider_idshown_eta_secsurge_multipliertrip_id
1900142falsetruetrue4802025-08-01 08:01:0010142011
1900242truefalsefalse3602025-08-01 08:03:001023601.22
2900342falsetruefalse4202025-08-15 18:30:001033001.13
1900142falsetruetrue3602025-08-20 09:00:0010133014
2900442truefalsetrue6602025-08-28 22:10:001046001.55

Table riders

columntypeconstraints
rider_idBIGINTPRIMARY KEY
signup_dtDATENOT NULL
city_idINTNOT NULL

Sample data:

city_idrider_idsignup_dt
11012025-05-01
11022025-01-15
21032025-06-20
21042025-03-10

Expected output

One row per (city_id, calendar date 2025-08-01..2025-09-01): the 7-day rolling median of shown_eta_sec and the same-day completed/total conversion rate, with NULLs on zero-request days, ordered by city_id then request_date.

city_iddaily_conversionmedian_shown_eta_7d_secrequest_date
10.53902025-08-01
1NULLNULL2025-08-02
1NULLNULL2025-08-03
1NULLNULL2025-08-04
1NULLNULL2025-08-05
1NULLNULL2025-08-06
1NULLNULL2025-08-07
1NULLNULL2025-08-08
1NULLNULL2025-08-09
1NULLNULL2025-08-10
1NULLNULL2025-08-11
1NULLNULL2025-08-12
1NULLNULL2025-08-13
1NULLNULL2025-08-14
1NULLNULL2025-08-15
1NULLNULL2025-08-16
1NULLNULL2025-08-17
1NULLNULL2025-08-18
1NULLNULL2025-08-19
113302025-08-20
1NULLNULL2025-08-21
1NULLNULL2025-08-22
1NULLNULL2025-08-23
1NULLNULL2025-08-24
1NULLNULL2025-08-25
1NULLNULL2025-08-26
1NULLNULL2025-08-27
1NULLNULL2025-08-28
1NULLNULL2025-08-29
1NULLNULL2025-08-30
1NULLNULL2025-08-31
1NULLNULL2025-09-01
2NULLNULL2025-08-01
2NULLNULL2025-08-02
2NULLNULL2025-08-03
2NULLNULL2025-08-04
2NULLNULL2025-08-05
2NULLNULL2025-08-06
2NULLNULL2025-08-07
2NULLNULL2025-08-08
2NULLNULL2025-08-09
2NULLNULL2025-08-10
2NULLNULL2025-08-11
2NULLNULL2025-08-12
2NULLNULL2025-08-13
2NULLNULL2025-08-14
213002025-08-15
2NULLNULL2025-08-16
2NULLNULL2025-08-17
2NULLNULL2025-08-18
2NULLNULL2025-08-19
2NULLNULL2025-08-20
2NULLNULL2025-08-21
2NULLNULL2025-08-22
2NULLNULL2025-08-23
2NULLNULL2025-08-24
2NULLNULL2025-08-25
2NULLNULL2025-08-26
2NULLNULL2025-08-27
206002025-08-28
2NULLNULL2025-08-29
2NULLNULL2025-08-30
2NULLNULL2025-08-31
2NULLNULL2025-09-01

Hints

  1. 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.
  2. 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.
  3. Cast the completed count to numeric (completed::numeric / total) so the conversion rate isn't integer-divided to 0.

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

Table trips

columntypeconstraints
trip_idBIGINTPRIMARY KEY
request_tsTIMESTAMPNOT NULL
city_idINTNOT NULL
rider_idBIGINTNOT NULL
driver_idBIGINT
shown_eta_secINT
pickup_eta_secINT
is_completedBOOLEAN
is_canceledBOOLEAN
surge_multiplierNUMERIC
is_treatmentBOOLEAN
experiment_idINT

Sample data:

city_iddriver_idexperiment_idis_canceledis_completedis_treatmentpickup_eta_secrequest_tsrider_idshown_eta_secsurge_multipliertrip_id
1900142falsetruetrue4802025-08-01 08:01:0010142011
1900242truefalsefalse3602025-08-01 08:03:001023601.22
2900342falsetruefalse4202025-08-15 18:30:001033001.13
1900142falsetruetrue3602025-08-20 09:00:0010133014
2900442truefalsetrue6602025-08-28 22:10:001046001.55

Table riders

columntypeconstraints
rider_idBIGINTPRIMARY KEY
signup_dtDATENOT NULL
city_idINTNOT NULL

Sample data:

city_idrider_idsignup_dt
11012025-05-01
11022025-01-15
21032025-06-20
21042025-03-10

Expected output

For each city_id, the query returns pre- and post-period conversion rates for treated and control trips, plus the difference-in-differences uplift. On the sample data, some combinations are NULL because there are no trips of that type in that period.

city_iddid_upliftpost_ctrl_convpost_treat_convpre_ctrl_convpre_treat_conv
1NULLNULL101
2NULL10NULLNULL

Hints

  1. Start by labeling each trip as pre or post based on the truncated request_date, and as treatment or control using is_treatment.
  2. Aggregate conversion rates by city_id, period, and is_treatment, then pivot these into columns and compute the DiD formula using simple arithmetic.