SQL で機密データのエクスポートを確認する

SELECT
  data_class,
  actor_role,
  COUNT(*) AS export_attempts,
  SUM(CASE WHEN outcome = 'success' THEN 1 ELSE 0 END) AS completed_exports,
  SUM(CASE WHEN outcome <> 'success' THEN 1 ELSE 0 END) AS unsuccessful_exports,
  SUM(CASE WHEN review_status IN ('pending', 'escalated') THEN 1 ELSE 0 END)
    AS open_reviews
FROM data_export_audit_events
WHERE timestamp_utc >= now() - INTERVAL '30 days'
GROUP BY data_class, actor_role
ORDER BY open_reviews DESC, unsuccessful_exports DESC;