Handle repeated churn in SQL
Quick Overview
This question evaluates proficiency in time-based data manipulation and analytics, including handling repeated churn and resubscription, interval logic, deduplication, and per-user metric derivation using SQL and Python.
As part of analyzing the same promotion experiment, you need SQL that handles users who churn and later resubscribe.
Assume the following tables:
- experiment_assignments
- user_id BIGINT
- variant STRING -- control or free_month
- assigned_at TIMESTAMP This table already contains only users who qualified for the experiment, so you do not need to implement eligibility logic.
- subscription_periods
- user_id BIGINT
- period_start_at TIMESTAMP
- period_end_at TIMESTAMP NULL -- exclusive end time; NULL means the subscription is still active Each row represents one continuous subscription period. A user may appear in multiple rows if they churn and later resubscribe.
Key relationship: experiment_assignments.user_id = subscription_periods.user_id. All timestamps are in UTC.
For users assigned between 2025-01-01 00:00:00 UTC and 2025-01-31 23:59:59 UTC, write SQL that returns one row per assigned user with the following columns:
- user_id
- variant
- converted_within_7d: 1 if the user starts any subscription in the interval [assigned_at, assigned_at + 7 days)
- active_on_day_30: 1 if the user has an active subscription at exactly assigned_at + 30 days, even if they churned and resubscribed before day 30
- had_churn_and_return_before_day_30: 1 if the user has at least two distinct subscription periods with period_start_at < assigned_at + 30 days
After producing the user-level result, explain how you would aggregate it to variant-level conversion and 30-day retention metrics.