-- HAC Reduction Program: 719 Hospitals Penalized in FY2026
-- Fonteum re-derivation query — https://fonteum.com/research/hac-reduction-program-penalties
-- Data as of: 2026-06-03  ·  methodology: cms-hac-reduction-program/v1
-- Source: U.S. Centers for Medicare & Medicaid Services — HAC Reduction Program — Hospital file (https://data.cms.gov/provider-data/dataset/yq43-i98g) — retrieved 2026-06-03
-- License: U.S. Government Works (17 U.S.C. §105)

-- National FY2026 aggregate (scored = payment-reduction determination present)
select count(*) filter (where in_worst_quartile is not null) as scored,
       count(*) filter (where in_worst_quartile is true)     as penalized,
       count(*) filter (where in_worst_quartile is null)     as na,
       count(*)                                              as total,
       round(avg(total_hac_score) filter (where total_hac_score is not null), 3) as avg_score,
       round((percentile_cont(0.5) within group (order by total_hac_score))::numeric, 3)  as median_score,
       round((percentile_cont(0.75) within group (order by total_hac_score))::numeric, 3) as p75_score
from hac_reduction_program
where fiscal_year = 2026;
-- => scored 3012 · penalized 719 · na 43 · total 3055
--    avg 0.005 · median -0.045 · p75 0.379

-- By state; the rate denominator excludes hospitals with an N/A determination.
select state,
       count(*) filter (where in_worst_quartile is not null) as scored,
       count(*) filter (where in_worst_quartile is true)     as penalized,
       round(100.0 * count(*) filter (where in_worst_quartile is true)
             / nullif(count(*) filter (where in_worst_quartile is not null), 0), 1) as rate
from hac_reduction_program
where fiscal_year = 2026 and state is not null
group by state
having count(*) filter (where in_worst_quartile is not null) >= 20
order by scored desc
limit 20;
