-- What Hospitals Charge for the Same Procedure (2024)
-- Fonteum re-derivation query — https://fonteum.com/research/hospital-charges-by-drg
-- Data as of: 2026-06-12  ·  methodology: hospital-charges-by-drg/v1
-- Source: U.S. Centers for Medicare & Medicaid Services — CMS Medicare Inpatient Hospitals — by Provider and Service, data year 2024 (https://data.cms.gov/provider-summary-by-type-of-service/medicare-inpatient-hospitals) — released 2026-06-11, retrieved 2026-06-12
-- License: U.S. Government Works (17 U.S.C. §105)

-- Headline: charges vs payments, discharge-weighted across the whole file
SELECT
  count(*)                                                         AS rows,
  count(distinct ccn)                                             AS hospitals,
  count(distinct ms_drg_code)                                     AS drgs,
  sum(total_discharges)                                           AS discharges,
  round(sum(avg_covered_charges * total_discharges)
        / sum(total_discharges))                                  AS mean_covered_charge,
  round(sum(avg_total_payments  * total_discharges)
        / sum(total_discharges))                                  AS mean_total_payment,
  round((sum(avg_covered_charges * total_discharges)
        / sum(total_discharges))
      / (sum(avg_total_payments  * total_discharges)
        / sum(total_discharges)), 2)                              AS charge_to_payment_ratio
FROM inpatient_utilization_summary;

-- Cross-hospital charge distribution for one DRG (here a joint replacement)
SELECT
  min(avg_covered_charges)                                              AS min_charge,
  percentile_cont(0.10) WITHIN GROUP (ORDER BY avg_covered_charges)     AS p10,
  percentile_cont(0.50) WITHIN GROUP (ORDER BY avg_covered_charges)     AS median,
  percentile_cont(0.90) WITHIN GROUP (ORDER BY avg_covered_charges)     AS p90,
  max(avg_covered_charges)                                             AS max_charge
FROM inpatient_utilization_summary
WHERE ms_drg_code = '470';

-- Charge by state for one DRG (states with >= 5 reporting hospitals)
SELECT d.state,
  count(*)                                                            AS hospitals,
  round(sum(i.avg_covered_charges * i.total_discharges)
        / sum(i.total_discharges))                                    AS mean_charge
FROM inpatient_utilization_summary i
JOIN hospital_directory d ON d.ccn = i.ccn
WHERE i.ms_drg_code = '470'
GROUP BY d.state
HAVING count(*) >= 5
ORDER BY mean_charge DESC;
