Write SQL to compute signup and retention lift

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.