PracHub
QuestionsPremiumCoachesLearningGuidesInterview Prep

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.

  • hard
  • OpenAI
  • Data Manipulation (SQL/Python)
  • Data Scientist

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`.

Quick Answer: 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.

Last updated: May 7, 2026

Loading coding console...

PracHub

Master your tech interviews with 8,000+ real questions from top companies.

Product

  • Questions
  • Learning Tracks
  • Interview Guides
  • Resources
  • Premium
  • For Universities
  • Student Access

Browse

  • By Company
  • By Role
  • By Category
  • Topic Hubs
  • SQL Questions
  • Compare Platforms
  • Discord Community

Support

  • support@prachub.com
  • (916) 541-4762

Legal

  • Privacy Policy
  • Terms of Service
  • About Us

© 2026 PracHub. All rights reserved.

Related Coding Questions

  • Handle repeated churn in SQL - OpenAI (hard)
  • Compute churn with re-subscriptions - OpenAI (hard)
  • Debug and harden trial-assignment Python code - OpenAI (Medium)
  • Write SQL for post-trial conversion cohorts - OpenAI (Medium)
  • Write SQL to compute signup and retention lift - OpenAI (medium)