Write SQL for post-trial conversion cohorts
Company: OpenAI
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using the schema below, write SQL to compute, for users first exposed between 2025‑06‑01 and 2025‑06‑30 (inclusive), the intent‑to‑treat paid conversion rate within 60 days of first exposure, by variant (control vs. trial). Count each user once using their first exposure only. Exclude users who had any paid_started_at before their first exposure. Return variant, exposed_users, converters_60d, conversion_rate_60d, and 95% Wald CIs. Then add a second query for the triggered analysis restricted to users who actually started a trial (if variant='trial'). Finally, write a data‑quality query to flag anomalies: users with a trial but no prior exposure; multiple exposures on the same day; exposure timestamps after trial_started_at; or duplicate user_ids.
Schema:
- users(user_id INT, country STRING, created_at TIMESTAMP)
- exposures(user_id INT, exposed_at TIMESTAMP, variant STRING CHECK (variant IN ('control','trial')))
- trials(user_id INT, trial_started_at TIMESTAMP)
- payments(user_id INT, paid_started_at TIMESTAMP, plan STRING, revenue_usd DECIMAL(10,2))
ASCII samples:
users
+---------+---------+---------------------+
| user_id | country | created_at |
+---------+---------+---------------------+
| 1 | US | 2025-05-28 10:00:00 |
| 2 | US | 2025-06-02 09:00:00 |
| 3 | GB | 2025-06-10 14:30:00 |
| 4 | IN | 2025-06-15 21:10:00 |
| 5 | US | 2025-06-20 08:05:00 |
+---------+---------+---------------------+
exposures
+---------+---------------------+---------+
| user_id | exposed_at | variant |
+---------+---------------------+---------+
| 1 | 2025-06-01 12:00:00 | control |
| 2 | 2025-06-03 12:05:00 | trial |
| 2 | 2025-06-04 12:05:00 | trial |
| 3 | 2025-06-11 16:00:00 | trial |
| 4 | 2025-06-15 22:00:00 | control |
+---------+---------------------+---------+
trials
+---------+---------------------+
| user_id | trial_started_at |
+---------+---------------------+
| 2 | 2025-06-03 12:06:00 |
| 3 | 2025-06-12 10:00:00 |
+---------+---------------------+
payments
+---------+---------------------+-------+-------------+
| user_id | paid_started_at | plan | revenue_usd |
+---------+---------------------+-------+-------------+
| 1 | 2025-06-20 08:00:00 | plus | 20.00 |
| 2 | 2025-08-01 09:00:00 | pro | 40.00 |
| 3 | 2025-07-25 11:00:00 | plus | 20.00 |
+---------+---------------------+-------+-------------+
Assume timestamps are UTC; treat 60 days as DATE_DIFF('day', first_exposure, paid_started_at) BETWEEN 0 AND 60. Be careful to: (a) define first_exposure per user, (b) prevent leakage from pre‑exposure payments, and (c) avoid double counting across variants.
Overview: This question evaluates proficiency in SQL-based cohort construction, deduplication and exclusion logic, conversion-rate computation with 95% Wald confidence intervals, and the distinction between intent-to-treat and triggered analyses.
Intent-to-treat 60-day paid conversion by variant
Using the schema below, write SQL to compute, for users whose very first exposure is between 2025-06-01 and 2025-06-30 (inclusive), the intent-to-treat paid conversion rate within 60 days of first exposure, by variant (control vs. trial).
Requirements:
- Treat a user's **first-ever exposure** as their cohort entry point, and count each user only once using that first exposure and its variant.
- Only include users whose first exposure timestamp is between '2025-06-01 00:00:00' and '2025-06-30 23:59:59'.
- Exclude users who had any payment (payments.paid_started_at) **before** their first exposure.
- Define 60-day conversion in PostgreSQL as: `(CAST(paid_started_at AS DATE) - CAST(first_exposed_at AS DATE)) BETWEEN 0 AND 60`.
- Compute metrics by variant: `variant`, `exposed_users`, `converters_60d`, `conversion_rate_60d`, and 95% Wald confidence intervals (`ci_lower_95`, `ci_upper_95`). Use the standard Wald formula: if `p = converters_60d / exposed_users`, `se = sqrt(p * (1 - p) / exposed_users)`, then `ci_lower_95 = p - 1.96 * se` and `ci_upper_95 = p + 1.96 * se`.
- Assume all timestamps are UTC.
Schema:
- users(user_id INT, country VARCHAR, created_at TIMESTAMP)
- exposures(user_id INT, exposed_at TIMESTAMP, variant VARCHAR CHECK (variant IN ('control','trial')))
- trials(user_id INT, trial_started_at TIMESTAMP)
- payments(user_id INT, paid_started_at TIMESTAMP, plan STRING, revenue_usd DECIMAL(10,2))
Tables
users(user_id INT, country VARCHAR(2), created_at TIMESTAMP)
exposures(user_id INT, exposed_at TIMESTAMP, variant VARCHAR(10))
trials(user_id INT, trial_started_at TIMESTAMP)
payments(user_id INT, paid_started_at TIMESTAMP, plan VARCHAR(20), revenue_usd DECIMAL(10,2))
Hints
- Use ROW_NUMBER() over exposures to identify each user's first-ever exposure and its variant, then filter that first_exposed_at into the June 2025 window.
- Build a per-user conversion flag with an EXISTS subquery on payments within 0–60 days of first_exposed_at, then aggregate to compute the Wald confidence interval.
Triggered analysis: 60-day conversion for trial starters
Using the same schema and cohort definition as in Question 1, write a second SQL query for the **triggered analysis** restricted to users who actually started a trial.
Requirements:
- Start from the same cohort definition as in Question 1: users whose **first-ever exposure** is between '2025-06-01 00:00:00' and '2025-06-30 23:59:59', defined by the earliest exposure per user.
- Restrict to users whose first exposure variant is 'trial'.
- Further restrict to those users who actually started a trial (i.e., have a matching row in trials with the same user_id). You may assume `trial_started_at >= first_exposed_at` for a valid triggered user, and ignore users where this is not true.
- Exclude users with any payment before their first exposure (same rule as Question 1).
- Compute the 60-day paid conversion rate (same PostgreSQL definition: `(CAST(paid_started_at AS DATE) - CAST(first_exposed_at AS DATE)) BETWEEN 0 AND 60`) among this triggered population.
- Return: `variant`, `triggered_users`, `converters_60d`, `conversion_rate_60d`, and 95% Wald CIs.
Schema:
- users(user_id INT, country VARCHAR, created_at TIMESTAMP)
- exposures(user_id INT, exposed_at TIMESTAMP, variant VARCHAR CHECK (variant IN ('control','trial')))
- trials(user_id INT, trial_started_at TIMESTAMP)
- payments(user_id INT, paid_started_at TIMESTAMP, plan STRING, revenue_usd DECIMAL(10,2))
Tables
users(user_id INT, country VARCHAR(2), created_at TIMESTAMP)
exposures(user_id INT, exposed_at TIMESTAMP, variant VARCHAR(10))
trials(user_id INT, trial_started_at TIMESTAMP)
payments(user_id INT, paid_started_at TIMESTAMP, plan VARCHAR(20), revenue_usd DECIMAL(10,2))
Hints
- Reuse the first_exposure logic from the intent-to-treat query, then filter to variant = 'trial' and inner join to trials.
- Apply the same 60-day conversion window logic and Wald CI formula, but aggregate over the triggered population (users with a trial) instead of all exposed users.
Data-quality checks for exposure, trial, and payment events
Write a PostgreSQL query. Write a single SQL query to flag data-quality anomalies in the experiment data. The query should identify the following issues and return them in a unified result set:
1. Users with a trial but **no prior exposure**: a row exists in trials, but there is no exposure for that user with exposed_at on or before trial_started_at.
2. Users with **multiple exposures on the same day**: for a given user, more than one exposure where DATE(exposed_at) is the same.
3. **Exposure timestamps after trial_started_at**: an exposure event (exposed_at) that occurs strictly after the user's trial_started_at.
4. **Duplicate user_ids** in the users table: the same user_id appears more than once.
Return at least: `anomaly_type` (a short string label), `user_id`, and a human-readable `detail` describing the issue. You may UNION several subqueries, one per anomaly type.
Schema:
- users(user_id INT, country STRING, created_at TIMESTAMP)
- exposures(user_id INT, exposed_at TIMESTAMP, variant STRING CHECK (variant IN ('control','trial')))
- trials(user_id INT, trial_started_at TIMESTAMP)
- payments(user_id INT, paid_started_at TIMESTAMP, plan STRING, revenue_usd DECIMAL(10,2))
Tables
users(user_id INT, country VARCHAR(2), created_at TIMESTAMP)
exposures(user_id INT, exposed_at TIMESTAMP, variant VARCHAR(10))
trials(user_id INT, trial_started_at TIMESTAMP)
payments(user_id INT, paid_started_at TIMESTAMP, plan VARCHAR(20), revenue_usd DECIMAL(10,2))
Hints
- Write one subquery per anomaly type (e.g., trial without prior exposure, multiple exposures per day), then UNION ALL them together with a consistent set of columns.
- For multiple-exposure and duplicate-user checks, use GROUP BY with HAVING (or filtering on a derived count) to find counts greater than 1.