-- Who Polices the Banks: 4,954 Federal Enforcement Orders, Split Almost Evenly.
-- Source: public.federal_banking_enforcement_actions
-- Source families: OCC EASearch CSV + Federal Reserve enforcement JSON.
-- Snapshot date: 2026-02-25.
-- Aggregate-only: this query selects agency counts and does not select party,
-- institution, document, or consumer fields.

WITH agency_counts AS (
  SELECT
    agency,
    count(*)::int AS enforcement_orders
  FROM public.federal_banking_enforcement_actions
  WHERE valid_to IS NULL
    AND snapshot_date = DATE '2026-02-25'
  GROUP BY agency
),
totals AS (
  SELECT sum(enforcement_orders)::int AS total_orders
  FROM agency_counts
)
SELECT
  CASE agency
    WHEN 'federal-reserve' THEN 'Federal Reserve'
    WHEN 'occ' THEN 'OCC'
    ELSE agency
  END AS agency,
  agency AS source_agency,
  enforcement_orders,
  round((enforcement_orders::numeric / total_orders) * 100, 1) AS share_pct
FROM agency_counts
CROSS JOIN totals
ORDER BY enforcement_orders DESC;

-- Expected rows from the 2026-02-25 committed snapshot:
-- Federal Reserve | federal-reserve | 2,514 | 50.7
-- OCC             | occ             | 2,440 | 49.3
-- Total           |                 | 4,954 | 100.0
