-- Barred but billable: excluded providers still enrolled in Medicare
-- Fonteum reproducible query — https://fonteum.com/research/excluded-providers-still-enrolled-2026
-- Data as of: 2026-06-14  ·  methodology: excluded-enrollment/v1
-- Source datasets: OIG LEIE, CMS PECOS
-- 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 billable: excluded providers still enrolled in Medicare —
-- fully reproducible query.
--
-- Question: how many providers the OIG has EXCLUDED from all federal health
-- programs still carry an ACTIVE Medicare enrollment record in PECOS?
--
-- Sources:
--   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, read-only.
--   public.pecos_providers     — CMS PECOS Ordering & Referring / provider-
--                                enrollment extract, federal release 2026-05-28,
--                                2,981,799 enrollment records / 2,556,656
--                                distinct NPIs. Presence in this file is an
--                                active, approved Medicare enrollment record
--                                eligible to order and refer. 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 LEIE rows with no NPI are excluded
-- from the matchable denominator and reported separately (see no-NPI query).
--
-- "In force" mirrors the production exclusion lookup (src/lib/exclusions): a row
-- is in force when reinstatement_date IS NULL OR reinstatement_date > today.
-- The OIG drops reinstated parties from the published LEIE, so this set equals
-- "any LEIE row by NPI". Date basis: current_date (2026-06-14 at publish).
--
-- Every headline figure in the study resolves to one of the rows below.

WITH leie_inforce AS (              -- distinct in-force, NPI-identified federal 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)
),
pecos_npi AS (                      -- distinct NPIs with an active PECOS enrollment record
  SELECT DISTINCT nullif(btrim(npi), '') AS npi
  FROM public.pecos_providers
  WHERE nullif(btrim(npi), '') IS NOT NULL
)
SELECT
  (SELECT count(*) FROM leie_inforce)                                       AS leie_inforce_npi,        -- 6,880
  (SELECT count(*) FROM pecos_npi)                                          AS pecos_distinct_npi,      -- 2,556,656
  (SELECT count(*) FROM leie_inforce l JOIN pecos_npi p USING (npi))        AS excluded_and_enrolled,   -- 19
  (SELECT count(*) FROM leie_inforce l
     LEFT JOIN pecos_npi p USING (npi) WHERE p.npi IS NULL)                 AS excluded_not_enrolled,   -- 6,861
  (SELECT count(*) FROM public.oig_leie_exclusions
     WHERE nullif(btrim(npi), '') IS NULL)                                  AS leie_rows_no_npi,        -- 61,030
  (SELECT count(*) FROM public.oig_leie_exclusions)                         AS leie_total_rows;         -- 68,055
--  leie_inforce_npi  pecos_distinct_npi  excluded_and_enrolled  excluded_not_enrolled  leie_rows_no_npi  leie_total_rows
--        6,880           2,556,656                19                    6,861               61,030            68,055
--  => 19 / 6,880 = 0.28% of in-force NPI-identified federal exclusions still hold an active enrollment record.

-- Enrollment records vs distinct providers among the matched set (one NPI can
-- hold multiple enrollment records; one match is an organizational record):
WITH leie_inforce AS (
  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)
),
matched AS (
  SELECT p.npi, p.state_cd, p.provider_type_desc, p.org_name, p.last_name
  FROM public.pecos_providers p
  JOIN leie_inforce l ON l.npi = nullif(btrim(p.npi), '')
)
SELECT
  count(*)                                                            AS enrollment_records,    -- 20
  count(DISTINCT npi)                                                 AS distinct_providers,    -- 19
  count(*) FILTER (WHERE last_name IS NOT NULL AND last_name <> '')   AS individual_records,    -- 19
  count(*) FILTER (WHERE org_name  IS NOT NULL AND org_name  <> '')   AS org_records           -- 1
FROM matched;

-- Excluded-yet-enrolled providers by active-enrollment state (distinct NPI):
WITH leie_inforce AS (
  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)
)
SELECT p.state_cd AS state,
       count(DISTINCT p.npi) AS providers
FROM public.pecos_providers p
JOIN leie_inforce l ON l.npi = nullif(btrim(p.npi), '')
GROUP BY p.state_cd
ORDER BY providers DESC, state;
--  NY 3 · CA 2 · FL 2 · NC 2 · PA 2 · TX 2 · AR 1 · CO 1 · MI 1 · MT 1 · NV 1 · OH 1 · OK 1   (13 states)

-- Excluded-yet-enrolled providers by enrolled specialty (distinct NPI):
WITH leie_inforce AS (
  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)
)
SELECT p.provider_type_desc AS specialty,
       count(DISTINCT p.npi) AS providers
FROM public.pecos_providers p
JOIN leie_inforce l ON l.npi = nullif(btrim(p.npi), '')
GROUP BY p.provider_type_desc
ORDER BY providers DESC, specialty;
--  FAMILY PRACTICE 4 · CLINICAL SOCIAL WORKER 2 · INTERNAL MEDICINE 2 · then 11 specialties at 1 each
--  (SNF, Anesthesiology, Cardiology, CNS, Clinical Psychologist, Hematology/Oncology, Medical
--   Oncology, Mental Health Counselor, PT in private practice, Podiatry, Vascular Surgery) = 14 specialties.

-- Statutory basis of the matched exclusions (mandatory §1128(a) vs permissive §1128(b))
-- and the exclusion-date span — separates one-cycle refresh lag from standing failure:
WITH pecos_npi AS (
  SELECT DISTINCT nullif(btrim(npi), '') AS npi
  FROM public.pecos_providers
  WHERE nullif(btrim(npi), '') IS NOT NULL
),
per_npi AS (
  SELECT e.npi,
         min(e.excl_date)                              AS first_excl,
         bool_or(trim(e.exclusion_type) LIKE '1128a%') AS any_mandatory
  FROM public.oig_leie_exclusions e
  JOIN pecos_npi p ON p.npi = nullif(btrim(e.npi), '')
  WHERE (e.reinstatement_date IS NULL OR e.reinstatement_date > current_date)
    AND nullif(btrim(e.npi), '') IS NOT NULL
  GROUP BY e.npi
)
SELECT
  count(*)                                                                    AS matched_providers,         -- 19
  count(*) FILTER (WHERE any_mandatory)                                       AS mandatory_basis_providers, -- 8
  count(*) FILTER (WHERE first_excl >= current_date - INTERVAL '90 days')      AS excl_within_90d,           -- 17
  count(*) FILTER (WHERE first_excl <  current_date - INTERVAL '365 days')     AS excl_over_1yr,             -- 2
  min(first_excl)                                                             AS oldest_standing_exclusion, -- 2015-06-18
  (current_date - min(first_excl))                                            AS oldest_age_days            -- 4,013
FROM per_npi;
--  By statutory basis (distinct NPI): §1128b4 license action 10 · §1128a1 program-related conviction 6
--  · §1128a3 felony health-care fraud 1 · §1128a4 felony controlled substance 1 · §1128b1 ownership/control 1.
--  Mandatory §1128(a) conviction-based bases account for 8 of 19; the §1128(b) permissive bases, 11.
--  17 of 19 were excluded within the last 90 days (consistent with revocation lag behind the 2026-05-20
--  LEIE release); 2 have stood for over a year, the oldest since 2015-06-18 (~11 years) — not lag.

-- Rows with NO NPI — excluded from the matchable denominator above and reported
-- separately. These exclusions cannot be matched to PECOS by identifier at all,
-- so NPI-based enrollment screening cannot reach them.
SELECT
  count(*)                                              AS total_leie_rows,    -- 68,055
  count(*) FILTER (WHERE nullif(btrim(npi), '') IS NULL) AS rows_no_npi,       -- 61,030
  count(*) FILTER (WHERE nullif(btrim(npi), '') IS NOT NULL) AS rows_with_npi  -- 7,025
FROM public.oig_leie_exclusions;
