Cross Functional Collaboration and Partnership Questions

WITH exposures AS (
  SELECT user_id, exposed_at
  FROM model_exposures
  WHERE model_version = 'v1.2'
    AND exposed_at BETWEEN '2025-01-01' AND '2025-01-07'
),
activity_within_30 AS (
  SELECT e.user_id
  FROM exposures e
  LEFT JOIN events ev
    ON ev.user_id = e.user_id
   AND ev.occurred_at BETWEEN e.exposed_at AND e.exposed_at + INTERVAL '30 day'
  LEFT JOIN subscriptions s
    ON s.user_id = e.user_id
   AND (
        -- active subscription at any time in 30-day window or cancellation after exposure
        (s.status = 'active' AND (s.canceled_at IS NULL OR s.canceled_at > e.exposed_at))
        OR (s.canceled_at BETWEEN e.exposed_at AND e.exposed_at + INTERVAL '30 day')
       )
  WHERE ev.event_id IS NOT NULL OR s.user_id IS NOT NULL
  GROUP BY e.user_id
)
SELECT
  COUNT(*) AS retained_30_count,
  (COUNT(*)::decimal / (SELECT COUNT(*) FROM exposures)) AS retained_30_rate
FROM activity_within_30;