-- Barred, but still listed: excluded providers stay active in the provider directory
-- Fonteum reproducible query — https://fonteum.com/research/excluded-providers-active-in-nppes-2026
-- Data as of: 2026-06-20  ·  methodology: nppes-exclusion/v1
-- Source datasets: OIG LEIE, NPPES
-- 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 still listed: excluded providers stay active in the national
-- provider directory — fully reproducible query.
--
-- Question: of the providers the OIG has EXCLUDED from all federal health
-- programs (by NPI), how many are still listed as ACTIVE in NPPES — the
-- national provider identity registry that credentialing, claims, EHR, and
-- directory checks resolve a provider against? NPPES is an identity registry,
-- not a sanctions registry: it does not deactivate an NPI when its holder is
-- excluded, so a check that only confirms an NPI is "active in NPPES" can
-- clear a barred provider.
--
-- This is the IDENTITY-registry cut. It is distinct from the Medicare
-- BILLING-enrollment cut (excluded-providers-still-enrolled-2026, LEIE x PECOS)
-- and the ORDER/REFER-eligibility cut (excluded-providers-ordering-referring-2026):
-- PECOS gates who may bill/order and correctly drops nearly all excluded
-- providers; NPPES gates nothing — it is the directory of record for provider
-- identity, and it keeps them.
--
-- Sources (all public, read-only):
--   public.oig_leie_exclusions — OIG List of Excluded Individuals and Entities,
--                                federal monthly bulk download, release
--                                2026-05-08 (latest exclusion 2026-05-20),
--                                68,055 active records (7,025 carry an NPI).
--   public.nppes_providers     — CMS NPPES, the National Plan & Provider
--                                Enumeration System (the NPI registry),
--                                9,034,519 records, single monthly snapshot.
--                                npi_deactivation_date IS NULL => the NPI is
--                                active in NPPES.
--
-- Join key: NPI only (10-digit, btrim). We NEVER match on name — a name match
-- is not a defensible identity assertion, so LEIE rows with no NPI are excluded
-- from the matchable denominator and reported separately.
--
-- "In force" mirrors the production exclusion lookup (src/lib/exclusions):
--   LEIE in force = reinstatement_date IS NULL OR reinstatement_date > today.
-- The OIG file drops reinstated parties, so every in-force NPI is a standing
-- federal bar. Date basis: current_date (2026-06-20 at publish).
-- nppes_providers carries one NPI per row (0 duplicate-NPI rows), so the join
-- never multiplies. Methodology version: nppes-exclusion/v1.
--
-- Every headline figure in the study resolves to one of the rows below.

-- ── HEADLINE: excluded providers still active in the NPI registry ───────────
WITH leie_inforce AS (             -- distinct 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 length(nullif(btrim(npi), '')) = 10
    AND nullif(btrim(npi), '') <> '0000000000'
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
)
SELECT
  count(*)                                                                            AS leie_inforce_npi, -- 6,880
  count(n.npi)                                                                        AS found_in_nppes,   -- 6,150
  count(*) FILTER (WHERE n.npi IS NOT NULL AND n.npi_deactivation_date IS NULL)       AS active_in_nppes,  -- 6,123 (HEADLINE)
  count(*) FILTER (WHERE n.npi IS NOT NULL AND n.npi_deactivation_date IS NOT NULL)   AS deactivated,      -- 27
  count(*) FILTER (WHERE n.npi IS NULL)                                               AS not_in_nppes,     -- 730
  round(100.0 * count(*) FILTER (WHERE n.npi IS NOT NULL AND n.npi_deactivation_date IS NULL)
        / nullif(count(n.npi), 0), 1)                                                 AS pct_active_of_found, -- 99.6
  round(100.0 * count(*) FILTER (WHERE n.npi IS NOT NULL AND n.npi_deactivation_date IS NULL)
        / count(*), 1)                                                                AS pct_active_of_all   -- 89.0
FROM leie_inforce l
LEFT JOIN public.nppes_providers n ON n.npi = l.npi;
--  leie_inforce_npi  found_in_nppes  active_in_nppes  deactivated  not_in_nppes  pct_active_of_found  pct_active_of_all
--       6,880            6,150           6,123            27           730             99.6                89.0
--  => Of the 6,150 OIG-excluded providers who exist in NPPES, 6,123 (99.6%) are still listed ACTIVE.
--  => Across all 6,880 NPI-identified in-force exclusions, 6,123 (89.0%) hold an active NPPES record;
--     only 27 (0.4% of those found) carry an NPI deactivation. 730 are absent from NPPES entirely.

-- ── One deterministic row per excluded NPI (for the breakdown cuts below) ────
-- All breakdown tables operate on the ACTIVE-in-NPPES set (6,123). We pick one
-- LEIE row per NPI by most-recent exclusion date so each provider is counted once.
-- Reusable CTE (re-stated inline in each query below for copy-paste reproducibility):
--   WITH leie AS (
--     SELECT DISTINCT ON (nullif(btrim(npi),'')) nullif(btrim(npi),'') AS npi,
--            excl_date, exclusion_type, state, general_desc
--     FROM public.oig_leie_exclusions
--     WHERE nullif(btrim(npi),'') IS NOT NULL AND length(nullif(btrim(npi),''))=10
--       AND nullif(btrim(npi),'') <> '0000000000'
--       AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
--     ORDER BY 1, excl_date DESC
--   ),
--   active AS (
--     SELECT l.* FROM leie l JOIN public.nppes_providers n ON n.npi = l.npi
--     WHERE n.npi_deactivation_date IS NULL          -- 6,123 rows
--   )

-- ── How long they have been excluded yet stayed active in NPPES ─────────────
WITH leie AS (
  SELECT DISTINCT ON (nullif(btrim(npi),'')) nullif(btrim(npi),'') AS npi, excl_date
  FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi),'') IS NOT NULL AND length(nullif(btrim(npi),''))=10
    AND nullif(btrim(npi),'') <> '0000000000'
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
  ORDER BY 1, excl_date DESC
),
active AS (
  SELECT l.npi, date_part('year', age(current_date, l.excl_date)) AS yrs
  FROM leie l JOIN public.nppes_providers n ON n.npi = l.npi
  WHERE n.npi_deactivation_date IS NULL
)
SELECT
  count(*) FILTER (WHERE yrs < 1)                AS under_1yr,   -- 466
  count(*) FILTER (WHERE yrs >= 1 AND yrs < 5)   AS y1_4,        -- 2,127
  count(*) FILTER (WHERE yrs >= 5 AND yrs < 10)  AS y5_9,        -- 1,640
  count(*) FILTER (WHERE yrs >= 10 AND yrs < 20) AS y10_19,      -- 1,864
  count(*) FILTER (WHERE yrs >= 20)              AS y20_plus,    -- 26
  count(*) FILTER (WHERE yrs >= 5)               AS y5_plus,     -- 3,530 (57.7%)
  count(*) FILTER (WHERE yrs >= 10)              AS y10_plus,    -- 1,890 (30.9%)
  count(*)                                       AS total        -- 6,123
FROM active;
--  466 excluded <1yr · 2,127 1-4yr · 1,640 5-9yr · 1,864 10-19yr · 26 20yr+  (sum = 6,123)
--  => 3,530 (57.7%) have been excluded 5+ years and are still active in NPPES; 1,890 (30.9%) 10+ years.

-- ── By statutory basis of exclusion (SSA §1128 / 42 U.S.C. §1320a-7) ────────
WITH leie AS (
  SELECT DISTINCT ON (nullif(btrim(npi),'')) nullif(btrim(npi),'') AS npi, excl_date, exclusion_type
  FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi),'') IS NOT NULL AND length(nullif(btrim(npi),''))=10
    AND nullif(btrim(npi),'') <> '0000000000'
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
  ORDER BY 1, excl_date DESC
)
SELECT l.exclusion_type, count(*) AS active_in_nppes
FROM leie l JOIN public.nppes_providers n ON n.npi = l.npi
WHERE n.npi_deactivation_date IS NULL
GROUP BY l.exclusion_type ORDER BY count(*) DESC;
--  1128a1 (program-related conviction)      2,434
--  1128b4 (license revoke/suspend/surrender)1,783
--  1128a4 (felony controlled-substance)       704
--  1128a3 (felony health-care fraud)          500
--  1128a2 (patient abuse/neglect conviction)  295
--  1128b14 (default on HEAL loan obligation)  129
--  all other §1128 bases (12 codes)           278     (sum = 6,123)

-- ── By provider category (LEIE general_desc, top categories) ────────────────
WITH leie AS (
  SELECT DISTINCT ON (nullif(btrim(npi),'')) nullif(btrim(npi),'') AS npi, excl_date,
         general_desc
  FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi),'') IS NOT NULL AND length(nullif(btrim(npi),''))=10
    AND nullif(btrim(npi),'') <> '0000000000'
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
  ORDER BY 1, excl_date DESC
)
SELECT l.general_desc, count(*) AS active_in_nppes
FROM leie l JOIN public.nppes_providers n ON n.npi = l.npi
WHERE n.npi_deactivation_date IS NULL
GROUP BY l.general_desc ORDER BY count(*) DESC LIMIT 10;
--  IND- LIC HC SERV PRO 2,650 · PHYSICIAN (MD, DO) 2,080 · BUS OWNER/EXEC 335 ·
--  EMPLOYEE - PRIVATE S 194 · MEDICAL PRACTICE, MD 192 · OTHER BUSINESS 176 ·
--  INDIVIDUAL (UNAFFILI 99 · NURSING PROFESSION 61 · CHIROPRACTIC PRACT 60 · DENTAL PRACTICE 34.
--  Entity split (NPPES entity_type_code): 5,866 individuals · 257 organizations  (sum = 6,123).

-- ── By state of the excluded party (LEIE state) ─────────────────────────────
WITH leie AS (
  SELECT DISTINCT ON (nullif(btrim(npi),'')) nullif(btrim(npi),'') AS npi, excl_date, state
  FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi),'') IS NOT NULL AND length(nullif(btrim(npi),''))=10
    AND nullif(btrim(npi),'') <> '0000000000'
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
  ORDER BY 1, excl_date DESC
)
SELECT l.state, count(*) AS active_in_nppes
FROM leie l JOIN public.nppes_providers n ON n.npi = l.npi
WHERE n.npi_deactivation_date IS NULL
GROUP BY l.state ORDER BY count(*) DESC LIMIT 12;
--  CA 772 · FL 556 · OH 489 · NY 460 · TX 389 · PA 296 · MI 181 · IL 155 ·
--  WV 152 · NJ 149 · NV 144 · KY 135.  Top 12 = 3,878; all other states = 2,245  (total 6,123).
