## 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).