Write SQL to compute signup and retention lift
Company: OpenAI
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are analyzing an A/B test for a marketing campaign that offers a **free 1‑month trial**.
Assume all timestamps are in **UTC**.
## Tables
### `experiment_assignments`
One row per user assignment.
- `user_id` BIGINT
- `experiment_id` STRING
- `variant` STRING -- 'control' or 'treatment'
- `assigned_at` TIMESTAMP -- first time user was assigned (sticky)
### `trial_starts`
One row per trial start.
- `user_id` BIGINT
- `trial_started_at` TIMESTAMP
### `sessions`
One row per user session.
- `user_id` BIGINT
- `session_start_at` TIMESTAMP
## Metric definitions
- Experiment window: users assigned between **2025-01-01** and **2025-01-31** inclusive.
- **Signup (trial start) conversion**: user starts a trial within **7 days** after `assigned_at`.
- **D30 retention after signup**: for users who converted (started a trial within 7 days), the user is retained if they have **≥ 1 session** with `session_start_at` in the window **[trial_started_at + 30 days, trial_started_at + 37 days)**.
## Task
Write a single SQL query that outputs one row per `variant` with:
- `variant`
- `assigned_users`
- `converted_users`
- `signup_rate`
- `retained_users`
- `retention_rate_among_converted`
Notes:
- Use distinct users (not event counts).
- If a user has multiple trial starts, use the **earliest** one that satisfies the 7‑day conversion window after assignment.
- A user can have many sessions; any qualifying session counts as retained.
Overview: This prompt evaluates SQL proficiency in time-windowed cohort analysis, event deduplication, date arithmetic, and per-variant aggregation for A/B experiments, categorized under Data Manipulation (SQL/Python) and targeted at an intermediate-level Data Scientist role.
Write a single SQL query that, for users assigned between 2025-01-01 and 2025-01-31 (inclusive), computes per-variant assigned_users, converted_users (trial within 7 days of assignment using the earliest qualifying trial), signup_rate, retained_users (≥1 session in [trial_started_at + 30 days, trial_started_at + 37 days)), and retention_rate_among_converted.
Tables
experiment_assignments(user_id BIGINT, experiment_id VARCHAR, variant VARCHAR, assigned_at TIMESTAMP)
trial_starts(user_id BIGINT, trial_started_at TIMESTAMP)
sessions(user_id BIGINT, session_start_at TIMESTAMP)
Hints
- Filter the assignment window with assigned_at >= '2025-01-01' and < '2025-02-01' to handle timestamps cleanly.
- Identify conversions by joining trial_starts within [assigned_at, assigned_at + 7 days) and choosing MIN(trial_started_at) per user.