-- The Anatomy of the Federal Healthcare Blacklist — the OIG LEIE, by the numbers.
-- Source table: public.oig_leie_exclusions (HHS-OIG List of Excluded Individuals/Entities).
-- Snapshot: LEIE monthly file released 2026-05-08; queried 2026-06-25.
-- Aggregate-only: every query below publishes counts, never individual names.

-- Headline totals (individuals vs entities, NPI coverage, waivers, date span).
SELECT
  count(*)                                                            AS total,
  count(*) FILTER (WHERE last_name IS NOT NULL AND last_name <> '')   AS individuals,
  count(*) FILTER (WHERE business_name IS NOT NULL AND business_name <> ''
                    AND (last_name IS NULL OR last_name = ''))        AS entities,
  count(*) FILTER (WHERE npi IS NOT NULL AND npi NOT IN ('', '0'))    AS with_npi,
  count(*) FILTER (WHERE waiver_date IS NOT NULL)                     AS with_waiver,
  count(*) FILTER (WHERE reinstatement_date IS NOT NULL)              AS reinstated,
  count(DISTINCT exclusion_type)                                      AS distinct_authorities,
  count(DISTINCT state)                                               AS states_covered,
  min(excl_date)                                                      AS earliest_exclusion,
  max(excl_date)                                                      AS latest_exclusion
FROM oig_leie_exclusions;
-- total 68,055; individuals 66,454; entities 1,601; with_npi 7,025; with_waiver 3;
-- reinstated 0; distinct_authorities 22; states_covered 57;
-- earliest 1977-07-01; latest 2026-05-20.

-- By statutory exclusion authority (the §1128 categories).
SELECT exclusion_type, count(*) FROM oig_leie_exclusions GROUP BY exclusion_type ORDER BY 2 DESC;
-- 1128b4 27,907; 1128a1 20,579; 1128a2 6,795; 1128a3 4,792; 1128a4 2,960; 1128b14 1,824; ... (22 codes)

-- By basis: mandatory (§1128(a)) vs permissive (§1128(b)) vs other.
SELECT
  CASE WHEN exclusion_type LIKE '1128a%' THEN 'mandatory'
       WHEN exclusion_type LIKE '1128b%' THEN 'permissive'
       WHEN exclusion_type LIKE '1128A%' THEN 'civil_money_penalty'
       ELSE 'other' END AS basis,
  count(*)
FROM oig_leie_exclusions GROUP BY 1 ORDER BY 2 DESC;
-- mandatory 35,126; permissive 32,766; civil_money_penalty 91; other 72.

-- By state / territory (top 15 shown on the page; 57 jurisdictions in total).
SELECT state, count(*) FROM oig_leie_exclusions GROUP BY state ORDER BY 2 DESC;
-- CA 7,896; FL 6,816; TX 4,816; NY 3,538; OH 3,166; PA 2,882; ...

-- By the year the party was added (full annual series 1977-2026).
SELECT EXTRACT(YEAR FROM excl_date)::int AS yr, count(*)
FROM oig_leie_exclusions WHERE excl_date IS NOT NULL GROUP BY 1 ORDER BY 1;
-- 1997 1,703; 2016 2,785 (peak); 2024 2,616; ...

-- By provider category and specialty (source-published fields; top rows shown).
SELECT general_desc,   count(*) FROM oig_leie_exclusions GROUP BY 1 ORDER BY 2 DESC;
SELECT specialty_desc, count(*) FROM oig_leie_exclusions GROUP BY 1 ORDER BY 2 DESC;
-- NURSE/NURSES AIDE is the single largest specialty: 29,090 (42.7%).
