-- For-profit, nonprofit, or government: who owns America's hospitals, and which model makes money
-- Fonteum reproducible query — https://fonteum.com/research/hospital-ownership-margins
-- Data as of: 2026-05-24  ·  methodology: hospital-ownership-margins/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 ownership mix & margins — fully reproducible query.
--
-- Source:   CMS Healthcare Cost Report Information System (HCRIS), hospital
--           cost report form CMS-2552-10 (HOSP10FY2024.ZIP).
-- Snapshot: cms-hcris-hospital-2552-10 / 2026-05-24, 6,019 facilities.
-- Tables:   public.hospital_directory       (ownership_type from Worksheet S-2
--                                             Part I "Type of Control"; public, read-only)
--           public.hcris_facility_summary   (operating_margin_pct, total_margin_pct,
--                                             net_patient_revenue, net_income; public, read-only)
-- Join key: ccn (CMS Certification Number) — the same backbone used by
--           hospital-margin-gap and hospital-distress-rural-access.
--
-- Every headline figure in the study resolves to one of the rows below.

-- 1) Margin by ownership class — the central finding (facility-average lens).
SELECT
  d.ownership_type,
  count(*)                                                              AS hospitals,
  count(h.operating_margin_pct)                                         AS with_margin,
  round(avg(h.operating_margin_pct), 2)                                 AS avg_op_margin_pct,
  round((percentile_cont(0.5) WITHIN GROUP
         (ORDER BY h.operating_margin_pct))::numeric, 2)               AS median_op_margin_pct,
  round(avg(h.total_margin_pct), 2)                                     AS avg_total_margin_pct,
  count(*) FILTER (WHERE h.operating_margin_pct > 0)                    AS positive_margin,
  round(100.0 * count(*) FILTER (WHERE h.operating_margin_pct > 0)
        / nullif(count(h.operating_margin_pct), 0), 1)                  AS pct_positive,
  count(*) FILTER (WHERE h.operating_margin_pct < -5)                   AS distressed,
  round(100.0 * count(*) FILTER (WHERE h.operating_margin_pct < -5)
        / nullif(count(h.operating_margin_pct), 0), 1)                  AS pct_distressed
FROM public.hospital_directory d
JOIN public.hcris_facility_summary h ON h.ccn = d.ccn
GROUP BY d.ownership_type
ORDER BY avg_op_margin_pct DESC;
--  proprietary  1785  1730   +0.19   +6.91   +4.28  1124  65.0  468  27.1   (for-profit — only positive class)
--  nonprofit    3034  2996   -4.75   -1.77   +4.43  1320  44.1 1183  39.5
--  government   1200  1061  -62.38  -12.30   -0.56   221  20.8  717  67.6

-- 2) Revenue-weighted (dollar-weighted) operating margin per class — the
--    outlier-robust lens. Ranking is identical to (1); levels are all positive
--    because the simple average in (1) is dragged by tiny facilities.
SELECT
  d.ownership_type,
  round(100.0 * sum(h.net_income) / nullif(sum(h.net_patient_revenue), 0), 2)
                                                                       AS rev_weighted_op_margin_pct,
  round(sum(h.net_patient_revenue) / 1e9, 1)                          AS total_net_patient_rev_billions
FROM public.hospital_directory d
JOIN public.hcris_facility_summary h ON h.ccn = d.ccn
WHERE h.net_patient_revenue IS NOT NULL
GROUP BY d.ownership_type
ORDER BY rev_weighted_op_margin_pct DESC;
--  proprietary  16.39   168.5
--  nonprofit     9.02  1092.3
--  government     7.55   237.8

-- 3) National baseline (all classes pooled).
SELECT
  round(avg(operating_margin_pct), 2)                                  AS national_avg_op_margin,
  round((percentile_cont(0.5) WITHIN GROUP
         (ORDER BY operating_margin_pct))::numeric, 2)                AS national_median_op_margin,
  count(operating_margin_pct)                                          AS with_margin
FROM public.hcris_facility_summary;
--  -13.84   -1.46   5787

-- 4) The extreme-negative tail that inflates the government average: 84 public
--    hospitals report an operating margin below -100%. The named worst are
--    standalone STATE PSYCHIATRIC hospitals — funded by state appropriations,
--    so Medicare net patient revenue (the cost-report denominator) is a tiny
--    fraction of their real budget. This is why the government MEDIAN (-12.30%)
--    is the honest central tendency, not the -62.38% mean.
SELECT d.hospital_name, d.state, d.ccn, round(h.operating_margin_pct, 1) AS op_margin_pct
FROM public.hospital_directory d
JOIN public.hcris_facility_summary h ON h.ccn = d.ccn
WHERE d.ownership_type = 'government' AND h.operating_margin_pct IS NOT NULL
ORDER BY h.operating_margin_pct ASC
LIMIT 10;
--  OSAWATOMIE STATE HOSPITAL          KS  174022  -5305.3
--  TRENTON PSYCHIATRIC HOSPITAL       NJ  314013  -4326.4
--  FLORIDA STATE HOSPITAL             FL  100298  -3799.3
--  GREYSTONE PARK PSYCH. HOSPITAL     NJ  314016  -2799.3
--  ... (all top-10 are state psychiatric hospitals)

-- 5) For-profit ownership share by state (states with >= 40 reporting hospitals).
SELECT
  d.state,
  count(*)                                                             AS hospitals,
  count(*) FILTER (WHERE d.ownership_type = 'proprietary')             AS for_profit,
  round(100.0 * count(*) FILTER (WHERE d.ownership_type = 'proprietary')
        / count(*), 1)                                                 AS pct_for_profit
FROM public.hospital_directory d
JOIN public.hcris_facility_summary h ON h.ccn = d.ccn
GROUP BY d.state
HAVING count(*) >= 40
ORDER BY pct_for_profit DESC
LIMIT 8;
--  NV  53  33  62.3
--  LA 201 116  57.7
--  PR  61  35  57.4
--  TX 573 320  55.8
--  FL 269 145  53.9
