-- Two federal exclusion lists, and they don't match: the OIG LEIE–SAM.gov overlap
-- Fonteum reproducible query — https://fonteum.com/research/federal-exclusion-list-overlap-2026
-- Data as of: 2026-06-19  ·  methodology: federal-list-overlap/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 exclusion-list overlap: OIG LEIE vs SAM.gov
-- Methodology version: federal-list-overlap/v1
--
-- Question: of the providers under an *active* federal exclusion that can be
-- identified by a National Provider Identifier (NPI), how many appear on BOTH
-- federal lists — the HHS-OIG List of Excluded Individuals and Entities (LEIE)
-- and the GSA SAM.gov exclusion/debarment registry — versus only one?
--
-- Sources (public, read-only, U.S. Government Works / 17 U.S.C. §105):
--   oig_leie_exclusions — OIG LEIE monthly bulk download, release 2026-05-08
--                         (published current-active only; reinstated parties drop off)
--   sam_exclusions      — SAM.gov / GSA exclusion & debarment registry, as of 2026-06-19
--
-- Match key: the 10-digit NPI only, btrim-normalized. A name is NEVER used to
-- assert a cross-list match. Listings, not deduplicated identities, are counted
-- on each side before the distinct-NPI reduction; the comparison set is the
-- distinct NPI on each list. Aggregate counts only — no party is named.

WITH leie AS (
  -- LEIE is published current-active, so no in-force filter is needed.
  SELECT DISTINCT btrim(npi) AS npi
  FROM   oig_leie_exclusions
  WHERE  btrim(npi) ~ '^[0-9]{10}$'
),
sam AS (
  -- SAM accumulates terminated records, so restrict to in-force exclusions:
  -- termination_date is null or still in the future relative to today.
  SELECT DISTINCT btrim(npi) AS npi
  FROM   sam_exclusions
  WHERE  btrim(npi) ~ '^[0-9]{10}$'
    AND  (termination_date IS NULL OR termination_date > current_date)
),
u AS (SELECT npi FROM leie UNION SELECT npi FROM sam)
SELECT
  (SELECT count(*) FROM leie)                                              AS leie_active_npi,        -- 6,880
  (SELECT count(*) FROM sam)                                               AS sam_active_npi,         -- 4,694
  (SELECT count(*) FROM leie l WHERE     EXISTS (SELECT 1 FROM sam s WHERE s.npi=l.npi)) AS on_both,  -- 3,747
  (SELECT count(*) FROM leie l WHERE NOT EXISTS (SELECT 1 FROM sam s WHERE s.npi=l.npi)) AS leie_only,-- 3,133
  (SELECT count(*) FROM sam s  WHERE NOT EXISTS (SELECT 1 FROM leie l WHERE l.npi=s.npi)) AS sam_only,-- 947
  (SELECT count(*) FROM u)                                                 AS union_npi,              -- 7,827
  round(100.0 * (SELECT count(*) FROM leie l WHERE NOT EXISTS (SELECT 1 FROM sam s WHERE s.npi=l.npi))
              / (SELECT count(*) FROM leie), 1)                            AS leie_only_pct_of_leie,  -- 45.5
  round(100.0 * (SELECT count(*) FROM leie l WHERE     EXISTS (SELECT 1 FROM sam s WHERE s.npi=l.npi))
              / (SELECT count(*) FROM u), 1)                               AS both_pct_of_union;      -- 47.9

-- Context: NPI coverage on the federal lists is itself sparse, so this overlap
-- describes the identifier-matchable subset only.
--   SELECT count(*)                                          AS leie_total,        -- 68,055
--          count(*) FILTER (WHERE btrim(npi) ~ '^[0-9]{10}$') AS leie_with_npi,     -- 7,025 (10.3%)
--   FROM oig_leie_exclusions;
