-- DRG Code List with Medicare Payments and Volumes (2026)
-- Fonteum reproducible query — https://fonteum.com/research/medicare-drg-code-list-2026
-- Data as of: 2026-06-11  ·  methodology: drg-reference/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 cost-and-volume reference — fully reproducible query.
--
-- Question: across every MS-DRG that Medicare paid for inpatient care in the
-- most recent program year, what did each code cost and how often was it billed?
-- For each DRG: total discharges, average covered (billed) charges, average
-- Medicare payment, and the number of distinct hospitals reporting it — ranked.
--
-- 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 from
-- discharge-weighted averages below — never imputed.
--
-- Averages are DISCHARGE-WEIGHTED across hospitals, not a mean-of-means: each
-- hospital's per-stay figure is weighted by that hospital's discharge count, so
-- a 5-discharge hospital does not count the same as a 5,000-discharge one. This
-- reproduces the national per-stay average a payer would actually observe.
--
-- "Most recent program year" is resolved at query time, never hard-coded.

-- audit-claim: national-overview
WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT *
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL          -- drop CMS-suppressed cells
)

-- ============================================================================
-- (1) Headline overview for the most recent program year.
-- ============================================================================
SELECT
  (SELECT yr FROM latest)                                       AS data_year,
  count(*)                                                      AS hospital_drg_rows,
  count(DISTINCT ccn)                                           AS hospitals,
  count(DISTINCT ms_drg_code)                                   AS drgs,
  sum(total_discharges)                                         AS total_discharges,
  round(sum(avg_covered_charges   * total_discharges) / sum(total_discharges)) AS dw_avg_covered_charge,
  round(sum(avg_medicare_payments * total_discharges) / sum(total_discharges)) AS dw_avg_medicare_payment,
  round(sum(avg_total_payments    * total_discharges) / sum(total_discharges)) AS dw_avg_total_payment,
  round(
    sum(avg_covered_charges   * total_discharges)
    / nullif(sum(avg_medicare_payments * total_discharges), 0), 2
  )                                                             AS charge_to_medicare_multiple,
  round(
    sum(avg_covered_charges   * total_discharges)
    / nullif(sum(avg_medicare_payments * total_discharges), 0), 1
  )                                                             AS display_charge_to_medicare_multiple
FROM base;
--  data_year hospital_drg_rows hospitals drgs total_discharges dw_avg_covered_charge dw_avg_medicare_payment dw_avg_total_payment charge_to_medicare_multiple
--  2024          145,879        2,906     540    4,952,481           92,408                 15,166                18,360                6.09

-- ============================================================================
-- (2) THE REFERENCE — per-DRG aggregates, ranked by discharge volume.
--     This is the canonical table: every DRG present in the program year, with
--     discharge volume, billed charges, Medicare payment, and provider count.
--     Swap the ORDER BY to rank by cost (dw_avg_medicare_payment DESC) instead.
-- ============================================================================
-- audit-claim: volume-ranking
WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT *
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL
)
SELECT
  ms_drg_code,
  max(ms_drg_description)                                       AS ms_drg_description,
  count(DISTINCT ccn)                                           AS hospitals,
  sum(total_discharges)                                         AS discharges,
  round(sum(total_discharges) / 1000.0)::int                    AS hero_thousands,
  round(sum(avg_covered_charges   * total_discharges) / sum(total_discharges)) AS avg_covered_charge,
  round(sum(avg_medicare_payments * total_discharges) / sum(total_discharges)) AS avg_medicare_payment,
  round(sum(avg_total_payments    * total_discharges) / sum(total_discharges)) AS avg_total_payment
FROM base
GROUP BY ms_drg_code
ORDER BY discharges DESC;                                       -- full set: 540 rows
--  Highest-volume rows (top of the 540-row reference):
--  ms_drg_code description                                          hospitals discharges avg_covered_charge avg_medicare_payment
--  871 Septicemia/severe sepsis w/o MV >96 hrs, w/ MCC               2,661     577,119      90,381            15,524
--  291 Heart failure and shock, w/ MCC                               2,587     304,694      56,495            10,022
--  177 Respiratory infections and inflammations, w/ MCC             2,332     141,351      71,065            12,668
--  193 Simple pneumonia and pleurisy, w/ MCC                        2,442     136,649      60,101             9,868
--  872 Septicemia/severe sepsis w/o MV >96 hrs, w/o MCC             2,216     103,998      49,416             7,681
--  690 Kidney and urinary tract infections, w/o MCC                 2,133      91,493      38,134             5,986

-- ============================================================================
-- (3) Same reference, ranked by cost (Medicare payment per stay), volume floor
--     of 1,000 national discharges so the top is a stable reference, not a
--     handful-of-cases artifact. 260 of the 540 DRGs clear the 1,000 floor.
-- ============================================================================
-- audit-claim: cost-ranking
WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT *
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL
),
national AS (
  SELECT
    sum(avg_medicare_payments * total_discharges) / sum(total_discharges)
      AS all_drg_avg_medicare_payment
  FROM base
)
SELECT
  ms_drg_code,
  max(ms_drg_description)                                       AS ms_drg_description,
  count(DISTINCT ccn)                                           AS hospitals,
  sum(total_discharges)                                         AS discharges,
  round(sum(avg_covered_charges   * total_discharges) / sum(total_discharges)) AS avg_covered_charge,
  round(sum(avg_medicare_payments * total_discharges) / sum(total_discharges)) AS avg_medicare_payment,
  round(
    (sum(avg_medicare_payments * total_discharges) / sum(total_discharges))
    / (SELECT all_drg_avg_medicare_payment FROM national)
  )::int                                                          AS medicare_vs_all_drg_multiple,
  round(
    sum(avg_covered_charges * total_discharges)
    / nullif(sum(avg_medicare_payments * total_discharges), 0), 1
  )                                                               AS charge_to_medicare_multiple
FROM base
GROUP BY ms_drg_code
HAVING sum(total_discharges) >= 1000
ORDER BY avg_medicare_payment DESC
LIMIT 8;
--  ms_drg_code description                                          hospitals discharges avg_covered_charge avg_medicare_payment
--  018 CAR T-cell and other immunotherapies                            49       1,360    2,187,781           434,771
--  001 Heart transplant or implant of heart assist system, w/ MCC      87       1,814    1,604,490           295,327
--  003 ECMO or tracheostomy w/ MV >96 hrs (except face/mouth/neck)     272       6,749    1,160,394           209,580
--  004 Tracheostomy w/ MV >96 hrs, w/o major O.R. procedure            212       3,589      680,475           120,704
--  005 Liver transplant w/ MCC or intestinal transplant                53       1,043      819,999           116,001

-- The two public sepsis display figures, including the deterministic
-- floor-to-10,000 rendering behind "680k+".
-- audit-claim: sepsis-combined
WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT *
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL
),
per_drg AS (
  SELECT ms_drg_code, sum(total_discharges) AS discharges
  FROM base
  WHERE ms_drg_code IN ('871', '872')
  GROUP BY ms_drg_code
)
SELECT
  max(discharges) FILTER (WHERE ms_drg_code = '871') AS drg_871_discharges,
  max(discharges) FILTER (WHERE ms_drg_code = '872') AS drg_872_discharges,
  sum(discharges)                                    AS combined_discharges,
  ((floor(sum(discharges) / 10000.0) * 10)::int::text || 'k+') AS display_volume
FROM per_drg;
-- DRG 871 577,119 · DRG 872 103,998 · combined 681,117 · display 680k+

-- ============================================================================
-- (4) Distribution context: how many DRGs clear a meaningful volume floor, and
--     the median provider count + median Medicare payment across all DRGs.
-- ============================================================================
-- audit-claim: distribution
WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT *
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL
),
per_drg AS (
  SELECT
    ms_drg_code,
    count(DISTINCT ccn)                                         AS hospitals,
    sum(total_discharges)                                       AS discharges,
    sum(avg_medicare_payments * total_discharges) / sum(total_discharges) AS avg_medicare_payment
  FROM base
  GROUP BY ms_drg_code
)
SELECT
  count(*)                                                              AS drgs,
  count(*) FILTER (WHERE discharges >= 1000)                            AS drgs_ge_1000_discharges,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY hospitals))         AS median_hospitals_per_drg,
  round(percentile_cont(0.5) WITHIN GROUP (ORDER BY avg_medicare_payment))::int AS median_drg_medicare_payment
FROM per_drg;
--  drgs drgs_ge_1000_discharges median_hospitals_per_drg median_drg_medicare_payment
--  540          260                      53                     15,170

-- DRG 853's direct row and national volume rank.
-- audit-claim: drg-853
WITH latest AS (
  SELECT max(data_year) AS yr FROM public.inpatient_utilization_summary
),
base AS (
  SELECT *
  FROM public.inpatient_utilization_summary
  WHERE data_year = (SELECT yr FROM latest)
    AND total_discharges IS NOT NULL
),
per_drg AS (
  SELECT
    ms_drg_code,
    max(ms_drg_description) AS ms_drg_description,
    count(DISTINCT ccn) AS hospitals,
    sum(total_discharges) AS discharges,
    round(sum(avg_covered_charges * total_discharges) / sum(total_discharges)) AS avg_covered_charge,
    round(sum(avg_medicare_payments * total_discharges) / sum(total_discharges)) AS avg_medicare_payment
  FROM base
  GROUP BY ms_drg_code
),
ranked AS (
  SELECT *, rank() OVER (ORDER BY discharges DESC) AS volume_rank
  FROM per_drg
)
SELECT *
FROM ranked
WHERE ms_drg_code = '853';
-- DRG 853 · hospitals 1,779 · discharges 79,560 · covered charge $232,698
-- Medicare payment $40,557 · volume rank 11
