試用版から有料版への変換を計算する

WITH trials AS (
  SELECT
    account_id,
    MIN(timestamp_utc) AS trial_started_at,
    MIN(acquisition_source) AS acquisition_source
  FROM subscription_events
  WHERE event_name = 'trial_started'
    AND timestamp_utc >= now() - INTERVAL '120 days'
  GROUP BY account_id
),
paid AS (
  SELECT
    account_id,
    MIN(timestamp_utc) AS paid_at
  FROM subscription_events
  WHERE event_name = 'subscription_started'
  GROUP BY account_id
)
SELECT
  date_trunc('week', trials.trial_started_at) AS trial_week,
  trials.acquisition_source,
  COUNT(*) AS trial_accounts,
  SUM(CASE
    WHEN paid.paid_at >= trials.trial_started_at
      AND paid.paid_at < trials.trial_started_at + INTERVAL '30 days'
    THEN 1 ELSE 0
  END) AS paid_accounts,
  100.0 * SUM(CASE
    WHEN paid.paid_at >= trials.trial_started_at
      AND paid.paid_at < trials.trial_started_at + INTERVAL '30 days'
    THEN 1 ELSE 0
  END) / NULLIF(COUNT(*), 0) AS conversion_rate_pct
FROM trials
LEFT JOIN paid ON paid.account_id = trials.account_id
GROUP BY
  date_trunc('week', trials.trial_started_at),
  trials.acquisition_source
ORDER BY trial_week, acquisition_source;