Microsoft Business Model and Data Strategy Questions

WITH first_sub AS (
  -- pick each user's first subscription start per product (cohort assignment)
  SELECT
    user_id,
    product,
    DATE_TRUNC('month', MIN(start_date)) AS cohort_month
  FROM subscriptions
  GROUP BY user_id, product
),
usage_months AS (
  -- join usage to cohorts and compute months since cohort_month
  SELECT
    f.user_id,
    f.product,
    f.cohort_month,
    u.event_date,
    -- months_diff = number of whole months between cohort month and event month
    (EXTRACT(YEAR FROM u.event_date) - EXTRACT(YEAR FROM f.cohort_month)) * 12
      + (EXTRACT(MONTH FROM u.event_date) - EXTRACT(MONTH FROM f.cohort_month)) AS months_diff
  FROM first_sub f
  JOIN usage u
    ON u.user_id = f.user_id
   AND u.product = f.product
),
user_retention_flags AS (
  -- for each cohort user, compute whether they had any usage within the retention windows
  SELECT
    user_id,
    product,
    cohort_month,
    MAX(CASE WHEN months_diff BETWEEN 0 AND 2 THEN 1 ELSE 0 END) AS used_in_3_months,
    MAX(CASE WHEN months_diff BETWEEN 0 AND 5 THEN 1 ELSE 0 END) AS used_in_6_months,
    MAX(CASE WHEN months_diff BETWEEN 0 AND 11 THEN 1 ELSE 0 END) AS used_in_12_months
  FROM usage_months
  GROUP BY user_id, product, cohort_month
),
cohort_base AS (
  -- include cohort users who had no usage at all (zero flags)
  SELECT f.user_id, f.product, f.cohort_month
  FROM first_sub f
),
cohort_with_flags AS (
  SELECT
    b.product,
    b.cohort_month,
    COUNT(DISTINCT b.user_id) AS cohort_size,
    SUM(COALESCE(r.used_in_3_months, 0)) AS retained_3,
    SUM(COALESCE(r.used_in_6_months, 0)) AS retained_6,
    SUM(COALESCE(r.used_in_12_months, 0)) AS retained_12
  FROM cohort_base b
  LEFT JOIN user_retention_flags r
    ON b.user_id = r.user_id
   AND b.product = r.product
   AND b.cohort_month = r.cohort_month
  GROUP BY b.product, b.cohort_month
)
SELECT
  product,
  cohort_month,
  cohort_size,
  ROUND(100.0 * retained_3 / NULLIF(cohort_size,0), 2) AS retention_pct_3mo,
  ROUND(100.0 * retained_6 / NULLIF(cohort_size,0), 2) AS retention_pct_6mo,
  ROUND(100.0 * retained_12 / NULLIF(cohort_size,0), 2) AS retention_pct_12mo
FROM cohort_with_flags
ORDER BY product, cohort_month;