SaaS dashboard examples that show the SQL

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);

SELECT
  endpoint,
  COUNT(*) AS requests,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0),
    2
  ) AS error_rate_pct,
  approx_percentile_cont(latency_ms, 0.50) AS p50_ms,
  approx_percentile_cont(latency_ms, 0.95) AS p95_ms
FROM api_requests
GROUP BY endpoint
ORDER BY error_rate_pct DESC, requests DESC;

WITH account_impact AS (
  SELECT
    incident_id,
    account_id,
    COUNT(*) AS impacted_operations
  FROM service_events
  WHERE incident_id IS NOT NULL
    AND status = 'error'
  GROUP BY incident_id, account_id
)
SELECT
  i.incident_id,
  i.severity,
  COUNT(*) AS affected_accounts,
  SUM(ai.impacted_operations) AS failed_operations,
  SUM(a.monthly_revenue_usd) AS represented_mrr_usd
FROM account_impact ai
JOIN incidents i USING (incident_id)
JOIN accounts a USING (account_id)
GROUP BY i.incident_id, i.severity
ORDER BY failed_operations DESC;

SELECT
  model,
  COUNT(*) AS requests,
  SUM(input_tokens) AS input_tokens,
  SUM(output_tokens) AS output_tokens,
  ROUND(SUM(cost_usd), 4) AS estimated_cost_usd,
  ROUND(
    SUM(cost_usd)
    / NULLIF(SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END), 0),
    4
  ) AS cost_per_success_usd
FROM llm_requests
GROUP BY model
ORDER BY estimated_cost_usd DESC;

SELECT
  job_name,
  COUNT(*) AS completed_jobs,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0),
    2
  ) AS terminal_failure_rate_pct,
  ROUND(AVG(attempt_count), 2) AS average_attempts,
  approx_percentile_cont(duration_ms, 0.95) AS p95_duration_ms
FROM job_runs
GROUP BY job_name
ORDER BY terminal_failure_rate_pct DESC, completed_jobs DESC;

WITH milestones AS (
  SELECT
    account_id,
    MAX(CASE WHEN event_name = 'source_connected'
      AND status = 'success' THEN 1 ELSE 0 END) AS connected,
    MAX(CASE WHEN event_name = 'query_completed'
      AND status = 'success' THEN 1 ELSE 0 END) AS queried,
    MAX(CASE WHEN event_name = 'dashboard_created'
      AND status = 'success' THEN 1 ELSE 0 END) AS dashboarded
  FROM product_events
  GROUP BY account_id
)
SELECT 'signed_up' AS step, COUNT(*) AS accounts FROM accounts
UNION ALL
SELECT 'source_connected', SUM(connected) FROM milestones
UNION ALL
SELECT 'query_completed', SUM(queried) FROM milestones
UNION ALL
SELECT 'dashboard_created', SUM(dashboarded) FROM milestones;