計算淨收入和總收入保留

SELECT
  date_trunc('month', timestamp_utc) AS month,
  COUNT(*) AS starting_accounts,
  SUM(starting_mrr_usd) AS starting_mrr_usd,
  SUM(ending_mrr_usd) AS ending_mrr_usd,
  100.0 * SUM(ending_mrr_usd)
    / NULLIF(SUM(starting_mrr_usd), 0) AS net_revenue_retention_pct,
  100.0 * SUM(CASE
    WHEN ending_mrr_usd < 0.0 THEN 0.0
    WHEN ending_mrr_usd > starting_mrr_usd THEN starting_mrr_usd
    ELSE ending_mrr_usd
  END) / NULLIF(SUM(starting_mrr_usd), 0)
    AS gross_revenue_retention_pct
FROM account_mrr_snapshots
WHERE timestamp_utc >= now() - INTERVAL '12 months'
  AND starting_mrr_usd > 0.0
GROUP BY date_trunc('month', timestamp_utc)
ORDER BY month;