Quick Overview

This question evaluates proficiency in event-time analytics and cohort churn metric computation using SQL, focusing on correct identification of first qualifying events and aggregation by experimental variant.

Write SQL for repeat churn

Company: OpenAI

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: hard

Interview Round: Technical Screen

Write a SQL query to measure the performance of a free-month promotion experiment. Assume `experiment_users` already contains only users who were eligible for the experiment. Do not re-implement eligibility logic. Tables: 1. `experiment_users` - `user_id` BIGINT -- primary key - `variant` VARCHAR -- values: `control`, `free_month` - `assigned_at` TIMESTAMP -- experiment assignment time in UTC 2. `subscription_events` - `user_id` BIGINT -- foreign key to `experiment_users.user_id` - `event_ts` TIMESTAMP -- event time in UTC - `event_type` VARCHAR -- one of `signup`, `free_trial_start`, `paid_start`, `cancel`, `reactivate` Business definitions: - `eligible_users`: distinct users in `experiment_users`. - `signups_30d`: users whose first `signup` after `assigned_at` occurs within 30 days of assignment. - `paid_converted_60d`: users whose first `paid_start` after `assigned_at` occurs within 60 days of assignment. - `churned_90d_after_first_paid`: among users with a first `paid_start`, count users who have at least one `cancel` event within 90 days after that first `paid_start`. - A user may cancel, reactivate, and cancel again. Count the user at most once, using the first `cancel` after the first `paid_start`. Ignore `cancel` events that happen before the first `paid_start`. - Use only each user's first qualifying `signup` and first qualifying `paid_start` after assignment. Return one row per `variant` with these columns: - `variant` - `eligible_users` - `signups_30d` - `paid_converted_60d` - `churned_90d_after_first_paid` - `churn_rate_90d_after_first_paid` where the denominator is `paid_converted_60d`.

Overview: This question evaluates proficiency in event-time analytics and cohort churn metric computation using SQL, focusing on correct identification of first qualifying events and aggregation by experimental variant.

Read the full OpenAI Data Scientist interview experience this question came from

Write a SQL query that, for each experiment variant (control vs free_month), returns eligible_users, signups_30d, paid_converted_60d, churned_90d_after_first_paid, and churn_rate_90d_after_first_paid. Only use each user’s first signup and first paid_start after assigned_at, count cancels only after the first paid_start, and cap churn at the first cancel within 90 days of that first paid_start. The churn rate denominator is paid_converted_60d.

Tables

experiment_users(user_id BIGINT, variant VARCHAR, assigned_at TIMESTAMP)

subscription_events(user_id BIGINT, event_ts TIMESTAMP, event_type VARCHAR)

Hints

  1. Find each user’s first signup after assigned_at using MIN(event_ts) filtered to event_type = 'signup' and event_ts >= assigned_at.
  2. Similarly find the first paid_start after assigned_at; mark conversions if it is within 60 days.

Loading coding console...