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.
Quick Answer: 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.