-- Barred but order-eligible: excluded providers still cleared to order and refer in Medicare
-- Fonteum reproducible query — https://fonteum.com/research/excluded-providers-ordering-referring-2026
-- Data as of: 2026-06-15  ·  methodology: excluded-ordering/v1
-- Source datasets: CMS PECOS, 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).

-- Barred but order-eligible: excluded providers still cleared to order and
-- refer in Medicare — fully reproducible query.
--
-- Question: how many providers who carry an ACTIVE exclusion or sanction still
-- appear in the CMS Order and Referring file as eligible to order and refer in
-- Medicare? A match is a data-quality flag for primary-source review — never a
-- proven fraud claim on any named individual (no provider is named or ranked).
--
-- Sources (all public, read-only):
--   public.cms_order_referring  — CMS Order and Referring file (PECOS-derived),
--                                 federal release 2026-06-12, 2,008,019 NPI-keyed
--                                 records. Every row is a provider cleared to
--                                 order and/or refer in Medicare; boolean columns
--                                 mark which claim types (Part B, DME, HHA, PMD,
--                                 hospice).
--   public.oig_leie_exclusions  — OIG List of Excluded Individuals and Entities,
--                                 federal monthly bulk download, release 2026-05-08,
--                                 68,055 active records (6,880 carry an NPI).
--   public.sam_exclusions       — SAM.gov (GSA) federal exclusion registry,
--                                 snapshot ingested 2026-06-13, 167,582 records
--                                 (4,694 carry an NPI). Healthcare-aligned via
--                                 HHS-OIG / OPM debarment feeds.
--   public.state_exclusions     — 10-state Medicaid exclusion lists (NY, PA, MD,
--                                 WA, NC, IA, MS, MT, OH, GA), release through
--                                 2026-06-08, 22,602 records (5,124 carry an NPI).
--
-- Join key: NPI only (10-digit, btrim). We never match on name — a name match is
-- not a defensible identity assertion, so exclusion rows with no NPI cannot be
-- reached by this identifier join and are out of the matchable denominator.
--
-- "In force" mirrors the production exclusion lookup (src/lib/exclusions):
--   LEIE  in force = reinstatement_date IS NULL OR reinstatement_date > today
--   SAM   in force = termination_date  IS NULL OR termination_date  > today
--   state in force = reinstatement_date IS NULL OR reinstatement_date > today
-- Date basis: current_date (2026-06-15 at publish).

WITH oref AS (                       -- distinct order/refer-eligible NPIs
  SELECT DISTINCT nullif(btrim(npi), '') AS npi
  FROM public.cms_order_referring
  WHERE nullif(btrim(npi), '') IS NOT NULL
),
leie AS (                            -- in-force, NPI-identified federal OIG exclusions
  SELECT DISTINCT nullif(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)
),
sam AS (                             -- in-force, NPI-identified SAM.gov exclusions
  SELECT DISTINCT nullif(btrim(npi), '') AS npi
  FROM public.sam_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (termination_date IS NULL OR termination_date > current_date)
),
st AS (                              -- in-force, NPI-identified state Medicaid exclusions
  SELECT DISTINCT nullif(btrim(npi), '') AS npi
  FROM public.state_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
)
SELECT
  (SELECT count(*) FROM oref)                                     AS oref_distinct_npi,    -- 2,008,019
  (SELECT count(*) FROM leie)                                     AS leie_inforce_npi,     -- 6,880
  (SELECT count(*) FROM sam)                                      AS sam_inforce_npi,      -- 4,694
  (SELECT count(*) FROM st)                                       AS state_inforce_npi,    -- 4,851
  (SELECT count(*) FROM oref JOIN leie USING (npi))               AS oref_x_leie,          -- 18
  (SELECT count(*) FROM oref JOIN sam  USING (npi))               AS oref_x_sam,           -- 6
  (SELECT count(*) FROM oref JOIN st   USING (npi))               AS oref_x_state,         -- 156
  (SELECT count(*) FROM oref
     WHERE npi IN (SELECT npi FROM leie
                   UNION SELECT npi FROM sam
                   UNION SELECT npi FROM st))                     AS oref_x_any;           -- 170
--  oref_distinct_npi  leie  sam  state  x_leie  x_sam  x_state  x_any
--     2,008,019      6,880 4,694 4,851    18      6      156     170
--  => 170 / 2,008,019 = 0.0085% of order/refer-eligible NPIs match an active exclusion.
--  Per-source columns sum to 180 because 9 NPIs sit on more than one list (see overlap query).

-- Overlap structure of the 170 matched NPIs (which list(s) each sits on):
WITH oref AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.cms_order_referring WHERE nullif(btrim(npi),'') IS NOT NULL),
leie AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.oig_leie_exclusions WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date)),
sam  AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.sam_exclusions      WHERE nullif(btrim(npi),'') IS NOT NULL AND (termination_date  IS NULL OR termination_date  > current_date)),
st   AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.state_exclusions    WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date)),
matched AS (
  SELECT o.npi,
         (o.npi IN (SELECT npi FROM leie)) AS in_leie,
         (o.npi IN (SELECT npi FROM sam))  AS in_sam,
         (o.npi IN (SELECT npi FROM st))   AS in_state
  FROM oref o
  WHERE o.npi IN (SELECT npi FROM leie UNION SELECT npi FROM sam UNION SELECT npi FROM st)
)
SELECT
  count(*)                                                                      AS total_matched,       -- 170
  count(*) FILTER (WHERE in_leie)                                               AS on_leie,             -- 18
  count(*) FILTER (WHERE in_sam)                                                AS on_sam,              -- 6
  count(*) FILTER (WHERE in_state)                                              AS on_state,            -- 156
  count(*) FILTER (WHERE (in_leie::int + in_sam::int + in_state::int) >= 2)     AS on_multiple_lists,   -- 9
  count(*) FILTER (WHERE in_leie AND in_state)                                  AS on_leie_and_state,   -- 7
  count(*) FILTER (WHERE in_state AND NOT in_leie AND NOT in_sam)               AS state_only,          -- 148
  count(*) FILTER (WHERE in_leie  AND NOT in_state AND NOT in_sam)              AS leie_only,           -- 10
  count(*) FILTER (WHERE in_sam   AND NOT in_state AND NOT in_leie)             AS sam_only             -- 3
FROM matched;

-- Order/refer privileges still attached to the 170 (one row per matched NPI):
WITH leie AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.oig_leie_exclusions WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date)),
sam  AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.sam_exclusions      WHERE nullif(btrim(npi),'') IS NOT NULL AND (termination_date  IS NULL OR termination_date  > current_date)),
st   AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.state_exclusions    WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date)),
m AS (
  SELECT DISTINCT ON (nullif(btrim(o.npi),'')) o.*
  FROM public.cms_order_referring o
  WHERE nullif(btrim(o.npi),'') IN (SELECT npi FROM leie UNION SELECT npi FROM sam UNION SELECT npi FROM st)
)
SELECT
  count(*)                                      AS matched,   -- 170
  count(*) FILTER (WHERE eligible_part_b)        AS part_b,    -- 144
  count(*) FILTER (WHERE eligible_dme)           AS dme,       -- 170
  count(*) FILTER (WHERE eligible_hha)           AS hha,       -- 135
  count(*) FILTER (WHERE eligible_pmd)           AS pmd,       -- 151
  count(*) FILTER (WHERE eligible_hospice)       AS hospice    -- 115
FROM m;

-- Lag vs standing failure: earliest exclusion date across all three lists per
-- matched NPI, bucketed. Separates one-refresh-cycle lag from standing records.
WITH oref AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.cms_order_referring WHERE nullif(btrim(npi),'') IS NOT NULL),
leie AS (SELECT nullif(btrim(npi),'') npi, excl_date d, exclusion_type et FROM public.oig_leie_exclusions WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date)),
sam  AS (SELECT nullif(btrim(npi),'') npi, exclusion_date d FROM public.sam_exclusions   WHERE nullif(btrim(npi),'') IS NOT NULL AND (termination_date  IS NULL OR termination_date  > current_date)),
st   AS (SELECT nullif(btrim(npi),'') npi, exclusion_date d FROM public.state_exclusions WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date)),
allx AS (SELECT npi, d FROM leie UNION ALL SELECT npi, d FROM sam UNION ALL SELECT npi, d FROM st),
matched AS (SELECT npi FROM oref WHERE npi IN (SELECT npi FROM allx)),
per AS (SELECT m.npi, min(a.d) AS first_excl FROM matched m JOIN allx a USING (npi) GROUP BY m.npi)
SELECT
  count(*)                                                                  AS matched,        -- 170
  count(*) FILTER (WHERE first_excl >= current_date - INTERVAL '90 days')    AS within_90d,     -- 24
  count(*) FILTER (WHERE first_excl >= current_date - INTERVAL '365 days')   AS within_1yr,     -- 60 (incl 90d)
  count(*) FILTER (WHERE first_excl <  current_date - INTERVAL '365 days')   AS over_1yr,       -- 105
  count(*) FILTER (WHERE first_excl <  current_date - INTERVAL '1825 days')  AS over_5yr,       -- 45
  count(*) FILTER (WHERE first_excl IS NULL)                                 AS no_excl_date,   -- 5
  min(first_excl)                                                           AS oldest_excl,    -- 1987-04-19
  (SELECT count(DISTINCT npi) FROM leie WHERE npi IN (SELECT npi FROM matched) AND trim(et) LIKE '1128a%') AS leie_mandatory, -- 7
  (SELECT count(DISTINCT npi) FROM leie WHERE npi IN (SELECT npi FROM matched))                            AS leie_matched    -- 18
FROM per;
--  60 within 1yr + 105 over 1yr + 5 no-date = 170. 105 standing > 1 year cannot be
--  explained by a one-cycle refresh lag; 45 have stood > 5 years; oldest 1987-04-19.

-- State-exclusion concentration (matches by state list; a provider excluded in
-- two states counts in each — sum 164 across 156 distinct NPIs):
WITH oref AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.cms_order_referring WHERE nullif(btrim(npi),'') IS NOT NULL),
st AS (SELECT nullif(btrim(npi),'') npi, state FROM public.state_exclusions WHERE nullif(btrim(npi),'') IS NOT NULL AND (reinstatement_date IS NULL OR reinstatement_date > current_date))
SELECT st.state, count(DISTINCT st.npi) AS providers
FROM st JOIN oref USING (npi)
GROUP BY st.state ORDER BY providers DESC, st.state;
--  NY 85 · PA 31 · MD 17 · WA 10 · NC 9 · IA 5 · MS 3 · MT 2 · OH 2  (164 across lists / 156 distinct NPIs)

-- SAM.gov matched exclusions by excluding agency (all individual classification):
WITH oref AS (SELECT DISTINCT nullif(btrim(npi),'') npi FROM public.cms_order_referring WHERE nullif(btrim(npi),'') IS NOT NULL)
SELECT s.classification, s.excluding_agency, count(DISTINCT nullif(btrim(s.npi),'')) AS providers
FROM public.sam_exclusions s JOIN oref o ON o.npi = nullif(btrim(s.npi),'')
WHERE (s.termination_date IS NULL OR s.termination_date > current_date)
GROUP BY s.classification, s.excluding_agency
ORDER BY providers DESC;
--  Individual · HHS 5 · OPM 1  (6 total)
