Write SQL/Python for CTR analytics
Company: Uber
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Part A — SQL (use the schema and sample data below): Compute 48-hour unique CTR for campaign_id=100 by variant, deduplicating to the earliest send per (user_id, campaign_id) on/after 2025-08-30, and excluding internal accounts user_id <= 2. Define CTR as distinct users with ≥1 click in [send_time, send_time + 48h] divided by distinct users sent (after deduplication). Return columns: campaign_id, variant, sends, unique_clickers_48h, ctr_48h. Then, also return a single row with the absolute lift (test_ctr − control_ctr). Optionally, include counts needed to compute a 95% CI for the lift using the normal approximation so it can be calculated downstream.
Schema:
users(user_id INT, signup_dt DATE, locale STRING)
email_sends(send_id INT, user_id INT, campaign_id INT, send_time TIMESTAMP, variant STRING) -- variant in ('control','test')
email_events(event_id INT, send_id INT, event_type STRING, event_time TIMESTAMP) -- event_type in ('open','click','unsubscribe')
Sample data:
users
+---------+------------+--------+
| user_id | signup_dt | locale |
+---------+------------+--------+
| 1 | 2025-08-20 | US |
| 2 | 2025-08-22 | US |
| 3 | 2025-08-25 | CA |
| 4 | 2025-08-27 | US |
| 5 | 2025-08-28 | GB |
email_sends
+---------+---------+------------+---------------------+----------+
| send_id | user_id | campaign_id| send_time | variant |
+---------+---------+------------+---------------------+----------+
| 10 | 1 | 100 | 2025-08-30 09:00:00 | control |
| 11 | 2 | 100 | 2025-08-30 09:00:00 | control |
| 12 | 3 | 100 | 2025-08-30 09:00:00 | test |
| 13 | 1 | 100 | 2025-08-31 09:00:00 | test | -- resend
| 14 | 4 | 101 | 2025-08-31 10:00:00 | control |
| 15 | 5 | 100 | 2025-08-30 09:00:00 | test |
email_events
+----------+---------+------------+---------------------+
| event_id | send_id | event_type | event_time |
+----------+---------+------------+---------------------+
| 1000 | 10 | open | 2025-08-30 09:05:00 |
| 1001 | 10 | click | 2025-08-30 09:06:00 |
| 1002 | 11 | open | 2025-08-30 09:10:00 |
| 1003 | 12 | open | 2025-08-30 09:07:00 |
| 1004 | 12 | unsubscribe| 2025-08-30 10:00:00 |
| 1005 | 13 | click | 2025-08-31 09:12:00 |
| 1006 | 15 | click | 2025-08-31 09:00:00 |
| 1007 | 14 | click | 2025-08-31 10:05:00 |
Part B — Python (pandas/sklearn): Given a DataFrame df with columns y_true (0/1), y_prob (predicted probability), and weight (non-negative), write code to: (1) plot the precision-recall curve; (2) find the threshold that maximizes weighted F1 using 'weight' as sample weights; (3) compute precision@top1% of the population by scoring, breaking ties deterministically. Then, explain why a model can have ROC-AUC=0.86 but PR-AUC=0.18 at 1% prevalence, and which metric you would optimize for a marketing CTR use case.
Overview: This question evaluates proficiency in data manipulation and analytics, covering SQL windowing and deduplication for time‑windowed cohort metrics (48‑hour unique CTR) as well as practical Python model evaluation tasks such as precision‑recall plotting, weighted F1 threshold selection, and precision@top‑k.
Read the full Uber Data Scientist interview experience this question came from
You are analyzing an A/B email campaign for **Uber**. Three tables describe the experiment:
- **`users`** — `user_id` (PK), `signup_dt`, `locale`.
- **`email_sends`** — one row per email sent: `send_id` (PK), `user_id`, `campaign_id`, `send_time` (timestamp), `variant` (`'control'` or `'test'`).
- **`email_events`** — engagement events on a send: `event_id` (PK), `send_id` (FK to `email_sends`), `event_type` (e.g. `'open'`, `'click'`, `'unsubscribe'`), `event_time` (timestamp).
Write **one PostgreSQL query** that computes the **48-hour unique click-through rate (CTR)** for **`campaign_id = 100`** by variant, and the absolute lift between test and control.
**Rules**
1. **Deduplicate sends:** for each `(user_id, campaign_id)` keep only the **earliest** send (break ties by smallest `send_id`), and only consider sends with `send_time >= '2025-08-30 00:00:00'`.
2. **Scope:** only `campaign_id = 100`.
3. **Exclude internal accounts:** drop sends where `user_id <= 2`.
4. **48-hour click window:** a deduplicated send counts as clicked if it has at least one `email_events` row with `event_type = 'click'` whose `event_time` falls in `[send_time, send_time + 48 hours]` (inclusive).
5. **CTR per variant** = (distinct users with ≥1 click in the 48h window) / (distinct users sent, after dedup). If a variant has **zero** sends, its CTR is **0**.
**Output:** return exactly **three rows**, in this order:
- one row for `variant = 'control'`,
- one row for `variant = 'test'`,
- one row for `variant = 'lift'` (the absolute difference `test_ctr − control_ctr`).
Columns (in order): `campaign_id`, `variant`, `sends`, `unique_clickers_48h`, `ctr_48h`.
For the two variant rows, `sends` and `unique_clickers_48h` are the counts described above and `ctr_48h` is the CTR (numeric, rounded to 4 decimals). For the `'lift'` row, set `campaign_id = 100`, `variant = 'lift'`, `sends = NULL`, `unique_clickers_48h = NULL`, and `ctr_48h = test_ctr − control_ctr`. Sort the result so the rows appear in the order control, test, lift.
Tables
users(user_id INT, signup_dt DATE, locale VARCHAR(10))
email_sends(send_id INT, user_id INT, campaign_id INT, send_time TIMESTAMP, variant VARCHAR(20))
email_events(event_id INT, send_id INT, event_type VARCHAR(20), event_time TIMESTAMP)
Hints
- Deduplicate with ROW_NUMBER() OVER (PARTITION BY user_id, campaign_id ORDER BY send_time, send_id) and keep rn = 1, applying the campaign / date / internal-user filters first.
- Build a fixed 2-row variant scaffold and LEFT JOIN to it so a variant with zero sends still produces a row (with CTR 0).