AI chat

WITH cohort AS (
  SELECT account_id,
         DATE_TRUNC('month', started_at) AS cohort_month,
         SUM(amount) AS starting_mrr
  FROM subscriptions
  WHERE status = 'active'
    AND started_at >= '2024-04-01'
  GROUP BY 1, 2
)
SELECT c.cohort_month,
       SUM(c.starting_mrr) AS baseline_mrr,
       SUM(CASE WHEN i.paid_at < '2024-07-01' THEN i.amount END) AS realized_mrr,
       ROUND(SUM(CASE WHEN i.paid_at < '2024-07-01' THEN i.amount END)
             / NULLIF(SUM(c.starting_mrr), 0) * 100, 1) AS nrr_pct
FROM cohort c
LEFT JOIN invoices i ON i.account_id = c.account_id
GROUP BY 1
ORDER BY 1 DESC;