← Openai Interview Insights

Openai·Data Scientist·Technical Phone Screen·Senior

Senior
Jun 2026

Summary

SQL-heavy technical screen for a DS role at OpenAI. One question, but it was dense enough to chew on for a while. The kind of problem where you think you're done and then realize you forgot to handle the multi-trial-start edge case.

Questions Asked (1)

Q1

You're analyzing an A/B test for a free trial campaign. Given tables for experiment assignments, trial starts, and user sessions, write a single SQL query that returns per-variant counts of assigned users, converted users, signup rate, retained users, and retention rate among converters. Conversion is defined as starting a trial within 7 days of assignment, and D30 retention means having at least one session in the window 30 to 37 days after the trial started. Use the earliest qualifying trial start if a user has multiple.

A/B Testing & ExperimentationProduct Analytics & MetricsData Modeling
Author's notes

The multi-trial-start rule is where I got tripped up.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Break the problem into sequential CTEs: first identify each user's earliest qualifying trial start within 7 days of assignment, then compute conversion status, then determine D30 retention by checking sessions in the 30-37 day window after trial start, and finally aggregate per variant. Use LEFT JOINs to preserve all assigned users and conditional aggregation for the metrics. Ensure the retention rate denominator is only converted users.

Pro tip: Explicitly state your assumptions about the data model (e.g., one assignment per user, session timestamps) and mention that you would validate edge cases like multiple trials or missing sessions before finalizing the query. This shows you think about data quality and metric definitions, which is crucial for A/B testing.

1. Identify earliest qualifying trial start per user

Join experiment assignments with trial starts on user_id, filter trials that started within 7 days after assignment, and use ROW_NUMBER() or MIN() to select the earliest trial start per user.

2. Determine conversion status

Flag users who have a qualifying trial start as converted; all assigned users should be included, with non-converters having NULL trial start.

3. Compute D30 retention among converters

For each converted user, check if they have at least one session between 30 and 37 days after their earliest trial start. Use a LEFT JOIN with sessions and a conditional flag.

4. Aggregate metrics per variant

Group by variant and calculate: COUNT(DISTINCT assigned users), COUNT(DISTINCT converted users), signup rate (converted/assigned), COUNT(DISTINCT retained users), and retention rate (retained/converted).

Key Points to Mention

  • Use of CTEs for readability and logical separation of steps
  • Handling multiple trials by selecting the earliest qualifying start
  • Definition of retention window: 30 to 37 days after trial start (inclusive/exclusive boundaries)
  • Ensuring retention rate denominator is converted users only
  • Use of LEFT JOINs to include all assigned users, even those without trials or sessions
  • Potential data quality checks: duplicate assignments, missing sessions, timezone consistency

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.