-- The OIG exclusion list, explained: who gets barred from Medicare, and why
-- Fonteum reproducible query — https://fonteum.com/research/oig-exclusion-patterns
-- Data as of: 2026-05-08  ·  methodology: oig-leie-patterns/v1
-- Source datasets: OIG LEIE
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- OIG Exclusion Patterns — fully reproducible query.
--
-- Source:   OIG List of Excluded Individuals and Entities (LEIE), monthly bulk download.
-- Snapshot: oig-leie / release 2026-05-08 (ingested 2026-05-25), 68,055 active records.
-- Table:    public.oig_leie_exclusions (public, read-only).
-- Authority: Social Security Act § 1128 / 42 U.S.C. § 1320a-7.
--
-- Every headline figure in the study resolves to one of the rows below.
-- Audit observation: 2026-07-14. Source release remains 2026-05-08.
-- audit-claim: leie-universe
WITH totals AS (
  SELECT
    count(*)                                                      AS total_records,
    count(*) FILTER (WHERE npi IS NOT NULL AND npi <> '')         AS npi_present,
    min(excl_date)                                               AS first_exclusion,
    max(excl_date)                                               AS latest_exclusion
  FROM public.oig_leie_exclusions
)
SELECT
  t.total_records,                                                          -- 68,055
  t.npi_present,                                                            -- 7,025
  round(100.0 * t.npi_present / t.total_records, 1)        AS npi_match_pct, -- 10.3%
  t.first_exclusion,                                                        -- 1977-07-01
  t.latest_exclusion                                                       -- 2026-05-20
FROM totals t;

-- audit-claim: statutory-authority-vector
SELECT
  trim(exclusion_type)                                       AS statutory_basis,
  count(*)                                                   AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 2)         AS pct
FROM public.oig_leie_exclusions
GROUP BY trim(exclusion_type)
ORDER BY records DESC
LIMIT 8;
--  1128b4   27,907  41.01   license revocation / surrender
--  1128a1   20,579  30.24   program-related conviction
--  1128a2    6,795   9.98   patient abuse / neglect
--  1128a3    4,792   7.04   felony health-care fraud
--  1128a4    2,960   4.35   felony controlled substance
--  1128b14   1,824   2.68   default on health-education loan
--  ...                       (b4 + a1 = 71.25% of the list)

-- audit-claim: exclusion-state-vector
SELECT
  trim(state)                                               AS state,
  count(*)                                                  AS records,
  round(100.0 * count(*) / sum(count(*)) OVER (), 1)        AS pct
FROM public.oig_leie_exclusions
GROUP BY trim(state)
ORDER BY records DESC
LIMIT 5;
--  CA  7,896  11.6
--  FL  6,816  10.0
--  TX  4,816   7.1
--  NY  3,538   5.2
--  OH  3,166   4.7
