Write SQL for repeat churn

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 .