-- Hospital charity care, by the numbers: who actually gives the most free care
-- Fonteum reproducible query — https://fonteum.com/research/hospital-charity-care
-- Data as of: 2026-05-24  ·  methodology: hospital-charity-care/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 Charity Care × Community Benefit — fully reproducible query.
--
-- Source:   CMS HCRIS Hospital Cost Reports (form CMS-2552-10), Worksheet S-10
--           uncompensated-care lines (charity care cost, bad debt expense),
--           FY2024 vintage.
-- Snapshot: cms-hcris-hospital-2552-10 / 2026-05-24
-- Archive:  https://downloads.cms.gov/FILES/HCRIS/HOSP10FY2024.ZIP
-- SHA-256:  e9552da36dcdcf904c5c4d35ffffe843af5b38afac196ac9db7db101bc8a96a0
-- Tables:   public.hcris_facility_summary  (public, read-only)
--           public.hospital_directory      (CCN-keyed ownership_type, public, read-only)
--
-- Charity care = care provided free or discounted under a hospital's financial
-- assistance policy (distinct from bad debt = billed care that went uncollected).
-- "Reporting" = charity_care_cost > 0. Ownership-type ratios are revenue-weighted
-- (sum of charity / sum of net patient revenue) over reporting hospitals.

-- Audit observation: 2026-07-14. Source snapshot remains 2026-05-24.
-- audit-claim: national-charity-summary
SELECT
  count(*)                                                         AS hospitals_total,        -- 6,019
  count(*) FILTER (WHERE charity_care_cost > 0)                    AS reporting_charity,       -- 4,135
  count(*) FILTER (WHERE bad_debt_expense  > 0)                    AS reporting_bad_debt,      -- 4,393
  round(sum(charity_care_cost) FILTER (WHERE charity_care_cost > 0)/1e9, 2) AS charity_billion,-- 27.68
  round(sum(bad_debt_expense)  FILTER (WHERE bad_debt_expense  > 0)/1e9, 2) AS bad_debt_billion,-- 12.38
  round(avg(charity_care_cost) FILTER (WHERE charity_care_cost > 0)/1e6, 2) AS avg_charity_mn, -- 6.70
  round((percentile_cont(0.5) WITHIN GROUP (ORDER BY charity_care_cost)
         FILTER (WHERE charity_care_cost > 0)/1e6)::numeric, 2)    AS median_charity_mn        -- 1.47
FROM public.hcris_facility_summary;

-- audit-claim: charity-by-ownership
--    ownership type. The tax-exempt nonprofit class gives the smallest share.
WITH j AS (
  SELECT d.ownership_type AS ot,
         h.charity_care_cost AS cc,
         h.net_patient_revenue AS npr
  FROM public.hcris_facility_summary h
  JOIN public.hospital_directory d ON d.ccn = h.ccn
)
SELECT
  ot,
  count(*)                                          AS facilities,            -- nonprofit 3,034 · proprietary 1,785 · government 1,200
  count(*) FILTER (WHERE cc > 0)                    AS reporting_charity,     -- 2,667 · 610 · 858
  round(100.0 * count(*) FILTER (WHERE cc > 0)
        / count(*), 1)                              AS report_rate_pct,      -- 87.9 · 34.2 · 71.5
  round(sum(cc) FILTER (WHERE cc > 0)/1e9, 2)       AS charity_billion,      -- 15.36 · 3.99 · 8.34
  round(100.0 * sum(cc) FILTER (WHERE cc > 0 AND npr > 0)
        / NULLIF(sum(npr) FILTER (WHERE cc > 0 AND npr > 0), 0), 2)
                                                    AS charity_pct_of_npr    -- 1.53 · 3.00 · 3.76
FROM j
GROUP BY ot
ORDER BY charity_pct_of_npr;

-- audit-claim: charity-concentration
WITH r AS (
  SELECT charity_care_cost AS cc,
         row_number() OVER (ORDER BY charity_care_cost DESC) AS rn,
         count(*)        OVER ()                              AS n,
         sum(charity_care_cost) OVER ()                       AS tot
  FROM public.hcris_facility_summary WHERE charity_care_cost > 0
)
SELECT
  round(100.0 * sum(cc) FILTER (WHERE rn <= 0.10 * n)/max(tot), 1) AS top_decile_share,   -- 65.4
  round(100.0 * sum(cc) FILTER (WHERE rn <= 50)/max(tot), 1)       AS top_50_share,       -- 26.5
  round(100.0 * sum(cc) FILTER (WHERE rn >  0.50 * n)/max(tot), 1) AS bottom_half_share   -- 3.1
FROM r;

-- audit-claim: large-nonprofit-under-one-percent
--    below 1% of revenue — the cohort the community-benefit debate centers on.
SELECT
  count(*) FILTER (WHERE h.charity_care_cost > 0) AS big_nonprofit_reporting,
  count(*) FILTER (
    WHERE h.charity_care_cost > 0
      AND h.charity_care_cost / h.net_patient_revenue < 0.01
  ) AS big_nonprofit_under_1pct
FROM public.hcris_facility_summary h
JOIN public.hospital_directory d ON d.ccn = h.ccn
WHERE d.ownership_type = 'nonprofit' AND h.net_patient_revenue > 250e6;
