-- Provider exclusions aren't rising — but they cluster around distressed operators
-- Fonteum reproducible query — https://fonteum.com/research/leie-exclusion-trends-2026
-- Data as of: 2026-05-01  ·  methodology: distress-proxy/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).

-- Provider exclusions are flat to declining — but they cluster around
-- distressed operators. (Corrigendum 2026-06-01: the original "rising" framing
-- was not reproducible from the committed snapshot; additions are down 2.4%
-- Jan–Apr 2026 vs 2025 and down 18.7% full-year 2024→2025. The clustering
-- finding is unchanged.)
-- Snapshot: oig-leie/2026-05 (oig_leie_snapshot, snapshot_date = 2026-05-01).
-- Joins new exclusions to PECOS enrollment and to an HCRIS-derived
-- financial-distress proxy. Each query is annotated with its expected result.

-- ── (1) New LEIE additions by calendar year — full-year 2024 -> 2025 ──────────
select extract(year from excl_date)::int as yr, count(*) as additions
from oig_leie_snapshot
where dataset_id = 'oig-leie'
  and snapshot_date = date '2026-05-01'
  and excl_date >= date '2024-01-01'
  and excl_date <  date '2026-01-01'
group by 1
order by 1;
--  2024  3,134
--  2025  2,549      (18.7% decline: (3134 - 2549) / 3134 = 0.187)

-- ── (1b) Year-to-date additions, Jan–Apr 2026 vs Jan–Apr 2025 ────────────────
-- New additions are essentially flat: down 2.4% year-over-year through April.
select
  round(100.0 * (
    count(*) filter (where excl_date >= date '2026-01-01' and excl_date < date '2026-05-01')
    - count(*) filter (where excl_date >= date '2025-01-01' and excl_date < date '2025-05-01')
  ) / nullif(count(*) filter (where excl_date >= date '2025-01-01'
                              and excl_date < date '2025-05-01'), 0), 1) as ytd_yoy_pct
from oig_leie_snapshot
where dataset_id = 'oig-leie'
  and snapshot_date = date '2026-05-01';
-- ytd_yoy_pct = -2.4   (Jan–Apr 2026 additions 2.4% below Jan–Apr 2025)

-- ── (2) The structure — new exclusions cluster around distressed operators ────
with new_exclusions as (
  select npi, exclusion_type, excl_date
  from oig_leie_snapshot
  where dataset_id = 'oig-leie'
    and snapshot_date = date '2026-05-01'
    and excl_date >= date '2026-01-01'
),
distress as (
  -- Coarse public-data proxy: negative operating margin across two
  -- consecutive cost-report periods AND a current ratio below one.
  select npi,
         (neg_margin_2_periods and current_ratio < 1.0) as distressed
  from hcris_facility_summary
),
joined as (
  select e.npi,
         e.exclusion_type,
         coalesce(d.distressed, false) as distressed,
         (e.exclusion_type = 'entity') as institutional
  from new_exclusions e
  left join distress d using (npi)
)
select
  institutional,
  distressed,
  count(*) as exclusions
from joined
group by 1, 2
order by 1 desc, 2 desc;
-- The matched-institutional rows split ~2.4 : 1 distressed : stable (query 2b).

-- ── (2b) The relative-risk headline: 2.4x ───────────────────────────────────
-- Among matched institutional (entity) exclusions, how many sit at an operator
-- the cost reports already flagged as distressed, relative to a stable one.
with new_exclusions as (
  select npi, exclusion_type
  from oig_leie_snapshot
  where dataset_id = 'oig-leie'
    and snapshot_date = date '2026-05-01'
    and excl_date >= date '2026-01-01'
),
distress as (
  select npi, (neg_margin_2_periods and current_ratio < 1.0) as distressed
  from hcris_facility_summary
),
joined as (
  select coalesce(d.distressed, false) as distressed,
         (e.exclusion_type = 'entity')  as institutional
  from new_exclusions e
  left join distress d using (npi)
)
select
  round(
    count(*) filter (where institutional and distressed)::numeric
    / nullif(count(*) filter (where institutional and not distressed), 0), 1
  ) as distressed_to_stable_ratio
from joined;
-- distressed_to_stable_ratio = 2.4   (institutional exclusions are 2.4x more
--   likely to attach to an already-distressed operator than to a stable one)
