-- Hospital Margin Gap Analysis (2024)
-- Fonteum re-derivation query — https://fonteum.com/research/hospital-margin-gap
-- Data as of: 2024  ·  methodology: hospital-margin/v1
-- Source: U.S. Centers for Medicare & Medicaid Services — CMS HCRIS Hospital Cost Reports (form CMS-2552-10) (https://www.cms.gov/Research-Statistics-Data-and-Systems/Downloadable-Public-Use-Files/Cost-Reports/Hospital-2010-form)
-- License: U.S. Government Works (17 U.S.C. §105)

-- Aggregates for one issuance_period of public.hospital_margin_index.
-- This is the body of the committed hospital_margin_dashboard(p_period)
-- function; margins come from CMS HCRIS form 2552-10 headline fields as
-- (net patient revenue - total operating expense) / net patient revenue * 100.
WITH base AS (
  SELECT state, operating_margin_pct, national_operating_margin_avg, distress_flag
  FROM public.hospital_margin_index
  WHERE issuance_period = :period
),
nat AS (
  SELECT
    count(*)                                                            AS total_count,
    count(*) FILTER (WHERE operating_margin_pct IS NOT NULL)            AS with_margin_count,
    count(*) FILTER (WHERE distress_flag)                               AS distress_count,
    max(national_operating_margin_avg)                                  AS national_avg,
    count(*) FILTER (WHERE operating_margin_pct < -10)                                AS b_lt_neg10,
    count(*) FILTER (WHERE operating_margin_pct >= -10 AND operating_margin_pct < -5) AS b_neg10_neg5,
    count(*) FILTER (WHERE operating_margin_pct >= -5  AND operating_margin_pct < 0)  AS b_neg5_0,
    count(*) FILTER (WHERE operating_margin_pct >= 0   AND operating_margin_pct < 5)  AS b_0_5,
    count(*) FILTER (WHERE operating_margin_pct >= 5   AND operating_margin_pct < 10) AS b_5_10,
    count(*) FILTER (WHERE operating_margin_pct >= 10)                                AS b_gte10
  FROM base
),
states AS (
  -- hospital_count counts rows with a non-NULL margin (the median's input);
  -- distress_count counts every flagged row in the state; states with zero
  -- non-NULL margins are excluded.
  SELECT
    state,
    count(*) FILTER (WHERE operating_margin_pct IS NOT NULL)            AS hospital_count,
    count(*) FILTER (WHERE distress_flag)                               AS distress_count,
    round((percentile_cont(0.5) WITHIN GROUP (ORDER BY operating_margin_pct))::numeric, 2) AS median_margin
  FROM base
  WHERE state IS NOT NULL
  GROUP BY state
  HAVING count(*) FILTER (WHERE operating_margin_pct IS NOT NULL) > 0
)
SELECT * FROM nat;
SELECT * FROM states ORDER BY median_margin DESC;
