Katkat
Back

Write SQL to compute signup and retention lift

OpenAIData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This prompt evaluates SQL proficiency in time-windowed cohort analysis, event deduplication, date arithmetic, and per-variant aggregation for A/B experiments, categorized under Data Manipulation (SQL/Python) and targeted at an intermediate-level Data Scientist role.

You are analyzing an A/B test for a marketing campaign that offers a free 1‑month trial.

Assume all timestamps are in UTC.

Tables

experiment_assignments

One row per user assignment.

  • user_id BIGINT
  • experiment_id STRING
  • variant STRING -- 'control' or 'treatment'
  • assigned_at TIMESTAMP -- first time user was assigned (sticky)

trial_starts

One row per trial start.

  • user_id BIGINT
  • trial_started_at TIMESTAMP

sessions

One row per user session.

  • user_id BIGINT
  • session_start_at TIMESTAMP

Metric definitions

  • Experiment window: users assigned between 2025-01-01 and 2025-01-31 inclusive.
  • Signup (trial start) conversion: user starts a trial within 7 days after assigned_at.
  • D30 retention after signup: for users who converted (started a trial within 7 days), the user is retained if they have ≥ 1 session with session_start_at in the window [trial_started_at + 30 days, trial_started_at + 37 days).

Task

Write a single SQL query that outputs one row per variant with:

  • variant
  • assigned_users
  • converted_users
  • signup_rate
  • retained_users
  • retention_rate_among_converted

Notes:

  • Use distinct users (not event counts).
  • If a user has multiple trial starts, use the earliest one that satisfies the 7‑day conversion window after assignment.
  • A user can have many sessions; any qualifying session counts as retained.