-- Same DRG, wildly different price: hospital charge variation, 2024
-- Fonteum reproducible query — https://fonteum.com/research/medicare-inpatient-drg-charge-variation-2026
-- Data as of: 2026-06-11  ·  methodology: drg-variation/v1
-- Source datasets: CMS Medicare Inpatient Hospitals
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Medicare inpatient DRG charge VARIATION — fully reproducible query.
--
-- Question: for the SAME MS-DRG, how widely do hospitals differ in what they
-- bill? We measure the hospital-to-hospital spread of average covered (billed)
-- charges per DRG — the 10th / median / 90th percentile across hospitals and
-- the 90/10 ratio — plus the per-DRG charge-to-Medicare-payment multiple.
-- This is DISTINCT from the charge-to-payment gap of the DRG cost reference:
-- here the axis is hospital-vs-hospital on one code, not charge-vs-payment.
--
-- Source:
--   public.inpatient_utilization_summary — CMS "Medicare Inpatient Hospitals,
--     by Provider and Service" public-use file (MUP_IHP). One row per
--     hospital (CCN) × MS-DRG × data_year. public, read-only.
--     Snapshot 2026-06-11; program year 2024 (the most recent annual release).
--     145,879 rows · 2,906 hospitals · 540 distinct MS-DRGs · 4,952,481 discharges.
--     License: US-Government-Works (17 U.S.C. §105).
--
-- Grain note: CMS suppresses any hospital-DRG cell with fewer than 11
-- discharges (total_discharges IS NULL). Those cells are excluded — never
-- imputed. avg_covered_charges is a per-hospital average for that DRG.
--
-- Percentiles are computed ACROSS HOSPITALS, one hospital = one observation
-- (unweighted), because the question is dispersion across hospitals, not the
-- per-stay national average. The charge-to-payment multiple, by contrast, is
-- discharge-weighted so it reproduces the national per-stay ratio.
--
-- High-volume universe: DRGs reported by >=100 hospitals over >=1,000 national
-- discharges (219 of the 540 codes) so each percentile is stable.
--
-- "Most recent program year" is resolved at query time, never hard-coded.

WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT ccn,
         ms_drg_code,
         ms_drg_description,
         total_discharges,
         avg_covered_charges::numeric    AS cc,   -- per-hospital avg billed charge
         avg_medicare_payments::numeric  AS mp    -- per-hospital avg Medicare payment
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL              -- drop CMS-suppressed cells
    AND avg_covered_charges IS NOT NULL
),
per_drg AS (
  SELECT
    ms_drg_code,
    max(ms_drg_description)                                        AS descr,
    count(DISTINCT ccn)                                            AS hospitals,
    sum(total_discharges)                                          AS discharges,
    round(percentile_cont(0.1) WITHIN GROUP (ORDER BY cc)::numeric) AS p10,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY cc)::numeric) AS median_charge,
    round(percentile_cont(0.9) WITHIN GROUP (ORDER BY cc)::numeric) AS p90,
    round(max(cc))                                                 AS max_charge,
    round((percentile_cont(0.9) WITHIN GROUP (ORDER BY cc)
          / nullif(percentile_cont(0.1) WITHIN GROUP (ORDER BY cc), 0))::numeric, 1)
                                                                   AS p90_p10_ratio,
    round(sum(cc * total_discharges) / sum(total_discharges))      AS dw_charge,
    round(sum(mp * total_discharges) / sum(total_discharges))      AS dw_pay,
    round((sum(cc * total_discharges)
          / nullif(sum(mp * total_discharges), 0))::numeric, 1)    AS charge_to_pay
  FROM base
  GROUP BY ms_drg_code
),
hi_vol AS (                                  -- the 219-DRG high-volume universe
  SELECT * FROM per_drg WHERE hospitals >= 100 AND discharges >= 1000
)

-- ============================================================================
-- (1) Headline: how wide is the spread across the high-volume universe?
--     The median DRG's 90/10 charge ratio is the lead figure (3.78×).
-- ============================================================================
SELECT
  count(*)                                                              AS drgs_qualifying,
  round(percentile_cont(0.25) WITHIN GROUP (ORDER BY p90_p10_ratio), 2) AS p25_ratio,
  round(percentile_cont(0.5)  WITHIN GROUP (ORDER BY p90_p10_ratio), 2) AS median_ratio,
  round(percentile_cont(0.75) WITHIN GROUP (ORDER BY p90_p10_ratio), 2) AS p75_ratio,
  min(p90_p10_ratio)                                                    AS min_ratio,
  max(p90_p10_ratio)                                                    AS max_ratio,
  count(*) FILTER (WHERE p90_p10_ratio >= 3)                            AS drgs_ge_3x,
  count(*) FILTER (WHERE p90_p10_ratio >= 4)                            AS drgs_ge_4x
FROM hi_vol;
--  drgs_qualifying p25  median p75  min  max  ge_3x ge_4x
--  219             3.52 3.78   4.02 2.62 5.01 210   58

-- ============================================================================
-- (2) The spread on the most COMMON codes — same DRG, priced across hospitals.
--     (Ranked by discharge volume; the study's first table.)
-- ============================================================================
SELECT ms_drg_code, descr, hospitals, p10, median_charge, p90, max_charge, p90_p10_ratio
FROM hi_vol
WHERE ms_drg_code IN ('871','291','177','193','683','065','470','190')
ORDER BY discharges DESC;
--  871 Sepsis w/ MCC               2,661  32,161  65,249 140,999 591,962 4.4
--  291 Heart failure w/ MCC        2,587  22,190  43,409  96,115 437,712 4.3
--  177 Respiratory infections      2,332  30,023  55,022 118,145 487,216 3.9
--  193 Simple pneumonia w/ MCC     2,442  24,559  46,587 101,466 375,925 4.1
--  683 Renal failure w/ CC         2,018  18,296  32,957  71,470 282,993 3.9
--  065 Intracranial hemorrhage     1,727  25,540  45,544 100,197 363,815 3.9
--  470 Hip/knee replacement        1,212  42,710  79,947 167,283 383,606 3.9
--  190 COPD w/ MCC                 1,595  22,754  41,850  86,174 314,888 3.8

-- ============================================================================
-- (3) The WIDEST-variation codes — same universe, ranked by 90/10 ratio.
-- ============================================================================
SELECT ms_drg_code, descr, hospitals, p10, median_charge, p90, p90_p10_ratio
FROM hi_vol
ORDER BY p90_p10_ratio DESC
LIMIT 10;
--  885 Psychoses                              562  17,820  36,077  89,309 5.0
--  897 Alcohol/drug abuse w/o rehab, w/o MCC  502  15,561  32,548  75,625 4.9
--  565 Other musculoskeletal dx w/ CC         119  19,575  42,155  91,081 4.7
--  542 Pathological fractures w/ MCC          184  38,338  68,552 177,576 4.6
--  196 Interstitial lung disease w/ MCC       217  35,405  80,187 161,953 4.6
--  809 Major hematological/immuno dx          203  27,887  55,001 128,304 4.6
--  554 Bone diseases/arthropathies w/o MCC    200  14,430  32,190  64,800 4.5
--  884 Organic disturbances/intellectual dis  707  25,102  49,954 112,207 4.5
--  103 Headaches w/o MCC                      110  22,920  46,101 100,811 4.4
--  871 Sepsis w/ MCC                        2,661  32,161  65,249 140,999 4.4

-- ============================================================================
-- (4) The OTHER gap — per-DRG charge-to-Medicare-payment multiple (a different
--     axis: charge-vs-payment, discharge-weighted). Included to separate it
--     from the hospital-to-hospital spread above.
-- ============================================================================
SELECT ms_drg_code, descr, dw_charge, dw_pay, charge_to_pay
FROM per_drg
WHERE ms_drg_code IN ('065','392','312','470','683','193','871','291')
ORDER BY charge_to_pay DESC;
--  065 Intracranial hemorrhage       56,315   7,474  7.5
--  392 Esophagitis/gastroenteritis   40,642   5,662  7.2
--  312 Syncope and collapse          46,199   6,636  7.0
--  470 Hip/knee replacement          92,976  14,289  6.5
--  683 Renal failure w/ CC           41,156   6,630  6.2
--  193 Simple pneumonia w/ MCC       60,101   9,868  6.1
--  871 Sepsis w/ MCC                 90,381  15,524  5.8
--  291 Heart failure w/ MCC          56,495  10,022  5.6

-- Overall, all DRGs: discharge-weighted charge $92,408 vs payment $15,166 =
-- 6.09× charge-to-Medicare multiple (the all-file figure of the cost reference).
