-- Rural hospital closures, by the numbers: which hospitals are most at risk
-- Fonteum reproducible query — https://fonteum.com/research/hospital-distress-rural-access-2024
-- Data as of: 2026-05-24  ·  methodology: hospital-distress-rural/v1
-- Source datasets: CMS Hospital Compare
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Hospital Financial Distress × Rural Access — fully reproducible query.
--
-- Source:   CMS HCRIS Hospital Cost Reports (form CMS-2552-10), FY2024 vintage.
-- Snapshot: cms-hcris-hospital-2552-10 / 2026-05-24
-- Archive:  https://downloads.cms.gov/FILES/HCRIS/HOSP10FY2024.ZIP
-- SHA-256:  e9552da36dcdcf904c5c4d35ffffe843af5b38afac196ac9db7db101bc8a96a0
-- Table:    public.hcris_facility_summary (public, read-only).
--
-- Distress threshold: operating_margin_pct < -5.0 (MedPAC / Moody's standard).
-- Rural cut: the CCN facility-type code (positions 3-6). 1300-1399 = Critical
-- Access Hospital; 0001-0879 = short-term acute. State = CCN positions 1-2
-- (CMS/SSA state code).
WITH c AS (
  SELECT
    operating_margin_pct                                   AS om,
    substring(ccn FROM 3 FOR 4)                            AS fac4,
    left(ccn, 2)                                           AS ssa
  FROM public.hcris_facility_summary
  WHERE operating_margin_pct IS NOT NULL
),
cls AS (
  SELECT
    om,
    (fac4 ~ '^[0-9]+$' AND fac4::int BETWEEN 1300 AND 1399) AS is_cah,
    (fac4 ~ '^[0-9]+$' AND fac4::int BETWEEN    1 AND  879) AS is_acute
  FROM c
)
SELECT
  count(*)                                                          AS hospitals_with_margin, -- 5,787
  round(avg(om), 2)                                                 AS avg_operating_margin,   -- -13.84
  count(*) FILTER (WHERE om < -5)                                   AS distressed_all,         -- 2,368
  -- Rural Critical Access Hospitals:
  count(*) FILTER (WHERE is_cah)                                    AS cah_total,              -- 1,354 (w/ margin; 1,375 incl null)
  round(avg(om) FILTER (WHERE is_cah), 2)                           AS cah_avg_margin,         -- -8.93
  count(*) FILTER (WHERE is_cah AND om < -5)                        AS cah_distressed,         -- 682
  round(100.0 * count(*) FILTER (WHERE is_cah AND om < -5)
        / count(*) FILTER (WHERE is_cah), 1)                        AS cah_distress_pct,       -- 50.4
  count(*) FILTER (WHERE is_cah AND om < -10)                       AS cah_under_neg10,        -- 497
  -- Urban short-term acute, for contrast:
  round(100.0 * count(*) FILTER (WHERE is_acute AND om < -5)
        / count(*) FILTER (WHERE is_acute), 1)                      AS acute_distress_pct      -- 39.2
FROM cls;

-- Median operating margin, rural vs urban:
--   CAH median   = -5.15   (already past the distress line)
--   acute median = -1.04

-- Distressed Critical Access Hospitals by state (CCN SSA prefix, top 5):
SELECT
  left(ccn, 2)                                              AS ssa_state_code,
  count(*) FILTER (
    WHERE substring(ccn FROM 3 FOR 4) ~ '^[0-9]+$'
      AND substring(ccn FROM 3 FOR 4)::int BETWEEN 1300 AND 1399
      AND operating_margin_pct < -5
  )                                                          AS distressed_cah
FROM public.hcris_facility_summary
GROUP BY left(ccn, 2)
ORDER BY distressed_cah DESC
LIMIT 5;
--  17 (Kansas)    76
--  45 (Texas)     70
--  28 (Nebraska)  32
--  27 (Montana)   29
--  24 (Minnesota) 29
