-- State Medicaid Exclusion Lists vs the Federal LEIE: The Gap
-- Fonteum reproducible query — https://fonteum.com/research/federal-state-exclusion-gap-2026
-- Data as of: 2026-07-14  ·  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 (13 loaded
--                                states as observed 2026-07-14).
--                                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. Audit date basis: DATE '2026-07-14'.
--
-- Every headline figure in the study resolves to one of the rows below.

-- audit-claim: federal-gap-vector
WITH se_inforce AS (
  SELECT state,
         nullif(btrim(npi), '') AS npi
  FROM public.state_exclusions
  WHERE reinstatement_date IS NULL OR reinstatement_date > DATE '2026-07-14'
),
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.4% 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 > DATE '2026-07-14')
),
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,949      3,188        1,761          64.4
--  NY       2,259      1,352          907          59.8
--  PA         973        568          405          58.4
--  OH         673        363          310          53.9
--  IA         313        263           50          84.0
--  MD         264        222           42          84.1
--  WA         210        178           32          84.8
--  NC         149        128           21          85.9
--  MS         138        106           32          76.8
--  NH          51         37           14          72.5
--  ND          47         28           19          59.6
--  MT          42         16           26          38.1   (small sample — see study)
--  GA          35          8           27          22.9   (small sample — see study)
--  TN           9          8            1          88.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.
-- audit-claim: state-record-vector
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 > DATE '2026-07-14') 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,916           6,640              2,259
--  PA        4,914           3,714                973
--  OH        1,980           1,248                673
--  MD        1,605           1,340                264
--  GA        1,369           1,333                 35
--  IA        1,149             794                313
--  WA          244              34                210
--  MS          193              53                138
--  NH           76              25                 51
--  ND          195             148                 47
--  MT          175             132                 42
--  NC          166              14                149
--  TN           21              12                  9
--  ALL      21,003          15,487              4,949

-- Raw source universes. These totals are deliberately separate from the
-- in-force, NPI-identified denominator above.
-- audit-claim: source-universes
SELECT
  (SELECT count(*) FROM public.state_exclusions) AS state_rows,
  (SELECT count(DISTINCT state) FROM public.state_exclusions) AS states,
  (SELECT count(*) FROM public.oig_leie_exclusions) AS leie_rows,
  (SELECT count(*) FROM public.oig_leie_exclusions
    WHERE nullif(btrim(npi), '') IS NOT NULL) AS leie_rows_with_npi;
-- state_rows 22,917 · states 13 · leie_rows 68,055 · leie_rows_with_npi 7,025
