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

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