-- The exclusion gap: federal screening misses most state Medicaid bars
-- Fonteum reproducible query — https://fonteum.com/research/federal-state-exclusion-gap-2026
-- Data as of: 2026-06-15  ·  methodology: exclusion-gap/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).

-- The federal–state exclusion gap — fully reproducible query.
--
-- Question: how many providers excluded by a STATE Medicaid program are
-- invisible to FEDERAL-only screening — i.e. carry no record on the OIG LEIE?
--
-- Sources:
--   public.state_exclusions    — State Medicaid exclusion lists (the State
--                                Exclusion Ring: ten states — NY OMIG, PA DHS,
--                                OH ODM, IA, MD, WA, NC, MS, MT, GA DCH-OIG).
--                                Public, read-only.
--   public.oig_leie_exclusions — OIG List of Excluded Individuals/Entities,
--                                federal monthly bulk download, release
--                                2026-05-08, 68,055 active records (7,025 with
--                                an NPI). Public, read-only.
--
-- Join key: NPI only (10-digit, btrim). We never match on name — a name match
-- is not a defensible identity assertion, so rows with no NPI are excluded from
-- the matchable denominator and reported separately (see no-NPI query below).
--
-- "In force" mirrors the production exclusion lookup (src/lib/exclusions): a row
-- is in force when reinstatement_date IS NULL OR reinstatement_date > today.
-- Applied to BOTH tables. Date basis: current_date (2026-06-15 at publish).
--
-- Every headline figure in the study resolves to one of the rows below.

WITH se_inforce AS (
  SELECT state,
         nullif(btrim(npi), '') AS npi
  FROM public.state_exclusions
  WHERE reinstatement_date IS NULL OR reinstatement_date > current_date
),
fed_inforce AS (
  -- Distinct federal NPIs in force. (The LEIE drops reinstated parties from the
  -- published file, so this set equals "any LEIE row by NPI" — the 64.3% gap is
  -- identical whether or not the in-force filter is applied on the federal side.)
  SELECT DISTINCT btrim(npi) AS npi
  FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
),
state_npi AS (              -- distinct NPI-identified, in-force provider per state
  SELECT DISTINCT state, npi FROM se_inforce WHERE npi IS NOT NULL
),
per_state AS (
  SELECT s.state,
         count(*)                                     AS matchable_providers,
         count(*) FILTER (WHERE f.npi IS NULL)        AS federally_invisible
  FROM state_npi s
  LEFT JOIN fed_inforce f USING (npi)
  GROUP BY s.state
),
overall_npi AS ( SELECT DISTINCT npi FROM se_inforce WHERE npi IS NOT NULL ),
overall AS (
  SELECT 'ALL'::text                                  AS state,
         count(*)                                     AS matchable_providers,
         count(*) FILTER (WHERE f.npi IS NULL)        AS federally_invisible
  FROM overall_npi o
  LEFT JOIN fed_inforce f USING (npi)
),
u AS ( SELECT * FROM per_state UNION ALL SELECT * FROM overall )
SELECT
  state,
  matchable_providers,                                              -- NPI-identified denominator
  federally_invisible,                                             -- NOT on the federal LEIE
  (matchable_providers - federally_invisible) AS on_federal_too,   -- caught by both
  round(100.0 * federally_invisible / nullif(matchable_providers, 0), 1)
                                              AS pct_invisible
FROM u
ORDER BY state;
--  state  matchable  invisible  on_federal_too  pct_invisible
--  ALL      4,851      3,117        1,734          64.3
--  NY       2,254      1,347          907          59.8
--  PA         974        569          405          58.4
--  OH         673        363          310          53.9
--  IA         313        263           50          84.0
--  MD         263        221           42          84.0
--  WA         210        178           32          84.8
--  NC         149        128           21          85.9
--  MS         138        106           32          76.8
--  MT          41         15           26          36.6   (small sample — see study)
--  GA          35          8           27          22.9   (small sample — see study)

-- Rows with NO NPI — excluded from the matchable denominator above and reported
-- separately. These in-force state exclusions cannot be matched to the federal
-- list by identifier at all, so federal NPI-based screening cannot reach them.
SELECT
  coalesce(state, 'ALL')                                          AS state,
  count(*) FILTER (WHERE inforce)                                 AS inforce_rows,
  count(*) FILTER (WHERE inforce AND npi IS NULL)                 AS inforce_no_npi_rows,
  count(DISTINCT npi) FILTER (WHERE inforce AND npi IS NOT NULL)  AS inforce_distinct_npi
FROM (
  SELECT state,
         nullif(btrim(npi), '') AS npi,
         (reinstatement_date IS NULL OR reinstatement_date > current_date) AS inforce
  FROM public.state_exclusions
) se
GROUP BY ROLLUP (state)
ORDER BY state NULLS LAST;
--  state  inforce_rows  inforce_no_npi_rows  inforce_distinct_npi
--  NY        8,904           6,633              2,254
--  PA        4,913           3,712                974
--  OH        1,980           1,248                673
--  MD        1,603           1,339                263
--  GA        1,369           1,333                 35
--  IA        1,150             795                313
--  WA          244              34                210
--  MS          193              53                138
--  MT          174             132                 41
--  NC          166              14                149
--  ALL      20,696          15,293              4,851
