Start by clarifying the schema and metric definitions, then build a base table of eligible users per variant. Use conditional aggregation with date arithmetic to compute each metric, and finally calculate churn rate as churned users divided by converted users.
Pro tip: Always state your assumptions about the schema and metric definitions before writing SQL—this shows you think like a data scientist, not just a coder. Also, use CTEs to break the query into logical steps, making it easier to debug and explain.
Ask about the columns in experiment_users and subscription_events, and confirm how to define eligible users, signups, paid conversions, and churn. This ensures you're solving the right problem.
Filter experiment_users to those who are eligible for the promotion (e.g., new users, correct experiment assignment) and group by variant. This forms the denominator for signup rate.
Join with subscription_events to count users who signed up within 30 days of eligibility and then converted to paid within 60 days of signup. Use date functions and conditional aggregation.
For users who converted, find their first paid start date and check if they churned within 90 days. Count these users per variant.
Divide churned users by converted users to get churn rate, and output one row per variant with all counts and the rate. Use a final SELECT with proper aliases.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.