-- Industry payments to providers on the OIG exclusion list
-- Fonteum reproducible query — https://fonteum.com/research/industry-payments-to-excluded-providers-2026
-- Data as of: 2026-06-14  ·  methodology: industry-payments-exclusions/v1
-- Source datasets: CMS Open Payments, 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).

-- Industry payments to OIG-excluded providers — fully reproducible query.
--
-- Question: how much did drug and device manufacturers pay, through CMS Open
-- Payments, to providers who appear on the federal OIG exclusion list (LEIE)?
--
-- Sources:
--   public.cms_open_payments    — CMS Open Payments, manufacturer-reported
--                                transfers of value under the Physician Payments
--                                Sunshine Act. Program year 2024 (the ingested
--                                snapshot). general / research / ownership
--                                record_types. 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 rows
--                                with an NPI; 6,880 distinct NPIs). 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 the 61,030 LEIE rows with no NPI are
-- excluded from the matchable denominator and reported separately (query 5).
--
-- "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 file, so this set equals every
-- LEIE NPI on record. Date basis: current_date (2026-06-14 at publish).
--
-- Every headline figure in the study resolves to one of the queries below.

-- The excluded-provider set, reused below: distinct in-force LEIE NPIs.
-- (Inlined as a CTE in each query so every query stands alone.)

-- 1) HEADLINE — total industry dollars to currently-excluded providers.
WITH excl AS (
  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 > current_date)
)
SELECT
  count(DISTINCT op.recipient_npi)     AS excluded_providers_paid,   -- 294
  count(*)                             AS payment_records,           -- 3,055
  round(sum(op.total_amount_usd), 2)   AS total_usd                  -- 3,835,221.38
FROM public.cms_open_payments op
JOIN excl ON op.recipient_npi = excl.npi;
--  excluded_providers_paid  payment_records  total_usd
--           294                  3,055        3,835,221.38

-- 2) BREAKDOWN BY PROGRAM YEAR. (The ingested snapshot holds program year 2024
--    only; as earlier years are added, this query extends backward unchanged.)
WITH excl AS (
  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 > current_date)
)
SELECT op.program_year,
       count(DISTINCT op.recipient_npi)   AS excluded_providers,
       count(*)                           AS payment_records,
       round(sum(op.total_amount_usd), 2) AS total_usd
FROM public.cms_open_payments op
JOIN excl ON op.recipient_npi = excl.npi
GROUP BY op.program_year
ORDER BY op.program_year;
--  program_year  excluded_providers  payment_records  total_usd
--      2024            294                3,055        3,835,221.38

-- 3) TOP PAYING MANUFACTURERS (by dollars to excluded providers).
WITH excl AS (
  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 > current_date)
)
SELECT op.manufacturer_name,
       count(DISTINCT op.recipient_npi)   AS excluded_providers_paid,
       count(*)                           AS payment_records,
       round(sum(op.total_amount_usd), 2) AS total_usd
FROM public.cms_open_payments op
JOIN excl ON op.recipient_npi = excl.npi
GROUP BY op.manufacturer_name
ORDER BY total_usd DESC
LIMIT 10;
--  manufacturer_name            excluded_providers  records  total_usd
--  Skye Orthobiologics LLC               2              2     3,196,265.00
--  CSL Plasma Inc.                       2            439       389,670.97
--  ConvaTec Inc.                         1             18        45,120.00
--  Teva Pharmaceuticals USA, Inc.        4             30        20,728.41
--  Intra-Cellular Therapies, Inc.       11             80        18,756.73
--  Alkermes, Inc.                        8             48        18,185.45
--  Otsuka America Pharmaceutical        18             60        16,207.26
--  AbbVie Inc.                          78            351        11,832.99   (widest reach)

-- 4) BY NATURE OF PAYMENT — where the dollars concentrate vs. where the contact
--    is broadest. Debt forgiveness is 85.1% of dollars; food & beverage reaches
--    274 of the 294 providers.
WITH excl AS (
  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 > current_date)
)
SELECT op.nature_of_payment,
       count(DISTINCT op.recipient_npi)   AS excluded_providers,
       count(*)                           AS payment_records,
       round(sum(op.total_amount_usd), 2) AS total_usd
FROM public.cms_open_payments op
JOIN excl ON op.recipient_npi = excl.npi
GROUP BY op.nature_of_payment
ORDER BY total_usd DESC;
--  nature_of_payment       providers  records  total_usd
--  Debt forgiveness            11        29     3,265,237.00
--  Consulting Fee               6       445       403,920.97
--  Food and Beverage          274     2,460        64,588.74
--  Compensation (speaking)      8        27        51,150.43
--  ... (long tail omitted)

-- 5) TIMING — payments dated AFTER the provider's exclusion took effect, vs. on
--    or before. Uses the earliest excl_date per NPI. A provider with payments on
--    both sides is counted in both rows.
WITH excl AS (
  SELECT btrim(npi) AS npi, min(excl_date) AS first_excl_date
  FROM public.oig_leie_exclusions
  WHERE nullif(btrim(npi), '') IS NOT NULL
    AND (reinstatement_date IS NULL OR reinstatement_date > current_date)
  GROUP BY btrim(npi)
)
SELECT
  CASE WHEN op.payment_date > e.first_excl_date
       THEN 'dated_after_exclusion'
       ELSE 'dated_on_or_before_exclusion' END        AS timing,
  count(DISTINCT op.recipient_npi)                     AS providers,
  count(*)                                             AS payment_records,
  round(sum(op.total_amount_usd), 2)                   AS total_usd
FROM public.cms_open_payments op
JOIN excl e ON op.recipient_npi = e.npi
GROUP BY 1
ORDER BY total_usd DESC;
--  timing                          providers  records  total_usd
--  dated_on_or_before_exclusion       118     2,199    3,625,463.77
--  dated_after_exclusion              181       855      209,757.61

-- 6) MATCHABLE DENOMINATOR — LEIE rows with vs. without an NPI. The 61,030 rows
--    with no NPI are unmatchable to Open Payments by identifier and are reported
--    separately, never matched by name.
SELECT
  count(*)                                                          AS total_rows,           -- 68,055
  count(*) FILTER (WHERE nullif(btrim(npi), '') IS NOT NULL)        AS rows_with_npi,        -- 7,025
  count(*) FILTER (WHERE nullif(btrim(npi), '') IS NULL)            AS rows_no_npi,          -- 61,030
  count(DISTINCT btrim(npi)) FILTER (WHERE nullif(btrim(npi),'') IS NOT NULL)
                                                                    AS distinct_npi          -- 6,880
FROM public.oig_leie_exclusions;
--  total_rows  rows_with_npi  rows_no_npi  distinct_npi
--    68,055        7,025        61,030        6,880
--  matched / matchable = 294 / 6,880 = 4.3% of NPI-identified excluded providers.
