Compute signup rate and retention from raw logs
Company: OpenAI
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
## Scenario
You are analyzing an A/B test for a marketing campaign offering **a free 1-month trial**.
You are given raw “upstream” tables that resemble production event logs rather than pre-aggregated metrics.
Assume **UTC timestamps** and that each user has **at most one assignment**.
## Tables (upstream)
### `experiment_assignment`
- `user_id` (STRING, PK)
- `experiment_id` (STRING)
- `variant` (STRING; 'control' or 'treatment')
- `assigned_at` (TIMESTAMP)
### `offer_exposure_events`
- `user_id` (STRING)
- `exposed_at` (TIMESTAMP)
- `campaign_id` (STRING)
### `subscription_events`
- `user_id` (STRING)
- `event_time` (TIMESTAMP)
- `event_type` (STRING; one of 'trial_start', 'paid_start', 'cancel')
### `app_sessions`
- `user_id` (STRING)
- `session_start` (TIMESTAMP)
## Metric definitions
1) **Signup rate (trial start within 7 days)**
- Denominator: users assigned to the experiment.
- Numerator: users with a `trial_start` event where `event_time` is in `[assigned_at, assigned_at + 7 days)`.
2) **D30 activity retention**
Among users who started a trial within 7 days, the user is retained if they have **≥ 1 app session** with `session_start` in `[assigned_at + 30 days, assigned_at + 37 days)`.
## Task
Write SQL to output one row per variant with:
- `variant`
- `assigned_users`
- `signup_users_7d`
- `signup_rate_7d`
- `retained_users_d30`
- `retention_rate_d30`
Also briefly describe what an “upstream” dataset/logging pipeline must contain to compute these metrics reliably (e.g., assignment logs, exposure logs, subscription lifecycle events, identity consistency).
Overview: This question evaluates the ability to manipulate event-level logs to compute cohort metrics—specifically signup rate and D30 retention—testing proficiency in SQL/Python, event-time windowing, joins, aggregation, and understanding of upstream logging semantics.
You are analyzing an A/B test for a marketing campaign that offers users a free month trial.
Definitions (use only the tables below):
- The experiment assignment population includes users assigned between 2025-05-01 and 2025-05-31 (inclusive).
- A user is considered "signed up" if they have at least one `signup` event in `event_log` between 2025-05-01 and 2025-05-31 (inclusive).
- Signup rate = (# signed up users) / (# assigned users), computed per variant.
- 7-day retention is computed on a signup cohort: users who signed up between 2025-05-01 and 2025-05-24 (inclusive).
- A signed-up user is "7-day retained" if they have at least one `app_open` event on the calendar date exactly 7 days after their signup date.
- 7-day retention rate = (# 7-day retained users) / (# eligible signed-up users), computed per variant.
Task:
Write a SQL query that returns one row per variant with:
- assigned_users
- signed_up_users
- signup_rate
- eligible_signup_users_7d
- retained_users_7d
- retention_rate_7d
Notes:
- If a user has multiple signup events, use the earliest signup date.
- Rates should be returned as decimals rounded to 4 decimal places.
Tables
experiment_assignment(user_id INT, variant VARCHAR(20), assigned_at DATE)
event_log(event_id INT, user_id INT, event_type VARCHAR(20), event_ts TIMESTAMP)
Hints
- Deduplicate signups by taking the earliest signup date per user.
- Compute 7-day retention using date arithmetic: app_open date = signup_date + 7 days.