-- SEC Enforcement Peaked in 2023 — and Has Fallen Every Year Since.
-- Source table: public.sec_enforcement_actions.
-- Snapshot: 2026-06-25.
-- Aggregate-only: these queries publish counts, not respondent captions or release-level records.

WITH current_actions AS (
  SELECT release_category, publish_date
  FROM public.sec_enforcement_actions
  WHERE valid_to IS NULL
    AND snapshot_date = DATE '2026-06-25'
)
SELECT
  count(*) FILTER (WHERE release_category = 'litigation_release') AS litigation_releases,
  count(*) FILTER (WHERE release_category = 'aaer') AS aaer,
  count(*) AS total_actions,
  round(
    100.0 * count(*) FILTER (WHERE release_category = 'aaer') / nullif(count(*), 0),
    1
  ) AS aaer_share_pct
FROM current_actions;
-- Expected: litigation_releases 11,866; aaer 3,317; total_actions 15,183; aaer_share_pct 21.8.

WITH current_actions AS (
  SELECT publish_date
  FROM public.sec_enforcement_actions
  WHERE valid_to IS NULL
    AND snapshot_date = DATE '2026-06-25'
    AND publish_date >= DATE '2020-01-01'
    AND publish_date < DATE '2027-01-01'
)
SELECT extract(year FROM publish_date)::int AS year, count(*) AS total_actions
FROM current_actions
GROUP BY 1
ORDER BY 1;
-- Expected: 2020 388; 2021 369; 2022 392; 2023 425; 2024 352; 2025 271; 2026 129.
