SaaS の健全性の概要 ダッシュボード

WITH account_activity AS (
  SELECT
    account_id,
    COUNT(*) AS successful_milestones
  FROM product_events
  WHERE status = 'success'
  GROUP BY account_id
),
account_reliability AS (
  SELECT
    account_id,
    COUNT(*) AS requests,
    SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS errors
  FROM api_requests
  GROUP BY account_id
)
SELECT
  COUNT(*) AS active_accounts,
  SUM(a.successful_milestones) AS successful_milestones,
  ROUND(100.0 * SUM(r.errors) / NULLIF(SUM(r.requests), 0), 2)
    AS api_error_rate_pct,
  SUM(ac.monthly_revenue_usd) AS represented_mrr_usd
FROM account_activity a
JOIN account_reliability r USING (account_id)
JOIN accounts ac USING (account_id);