-- Same procedure, different price: Medicare Part B charge variation, 2024
-- Fonteum reproducible query — https://fonteum.com/research/medicare-part-b-procedure-price-variation-2026
-- Data as of: 2026-06-14  ·  methodology: medicare-physician-service/v1
-- Source datasets: CMS Medicare Physician & Other Practitioners
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Medicare Part B PROCEDURE price variation — fully reproducible query.
--
-- Question: for the SAME outpatient/physician procedure (HCPCS code), in the
-- SAME care setting, how widely do providers differ in what they bill? We
-- measure the provider-to-provider spread of average submitted charge — the
-- 10th / median / 90th percentile across providers and the 90/10 ratio — plus
-- the per-procedure submitted-charge-to-Medicare-allowed multiple.
--
-- This is the OUTPATIENT / Part B companion to the inpatient DRG charge-
-- variation study: there the unit is a hospital × MS-DRG; here it is a
-- provider (NPI) × HCPCS code × place of service.
--
-- Source:
--   public.medicare_physician_service — CMS "Medicare Physician & Other
--     Practitioners — by Provider and Service" public-use file. One row per
--     provider (NPI) × HCPCS code × place of service × data_year. public, read-only. Snapshot ingested 2026-06-14; program year 2024 (the
--     most recent annual release). 9,781,673 rows · 1,207,473 providers ·
--     6,471 HCPCS codes · 2 places of service (F = facility, O = office /
--     non-facility). License: US-Government-Works (17 U.S.C. §105).
--
-- Grain note: each row is one provider's AVERAGE submitted charge for that
-- code in that setting. CMS suppresses any provider-code cell with fewer than
-- 11 beneficiaries; those rows are absent from the file — never imputed.
-- avg_submitted_charge is a provider average, not a per-claim figure.
--
-- One observation = one (NPI, HCPCS, place_of_service) cell. We pin place of
-- service into the grain because facility vs office (non-facility) billing
-- and global-vs-professional-component splits otherwise inflate the spread
-- (e.g. cataract removal pools to 35x across settings but is 4.7x within the
-- facility setting). Percentiles are computed ACROSS PROVIDERS, unweighted —
-- the question is dispersion across providers, not the per-service average.
--
-- Drug codes (hcpcs_drug_indicator = true) are excluded throughout — a drug's
-- charge tracks its acquisition cost, not procedure pricing discretion.
--
-- High-volume universe: code × setting cells billed by >=1,000 providers
-- (997 cells, 8.76M of 9.0M non-drug rows) 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.medicare_physician_service
),
base AS (
  SELECT npi,
         hcpcs_code,
         hcpcs_description,
         place_of_service,
         avg_submitted_charge::numeric    AS sc,   -- provider avg submitted charge
         avg_medicare_allowed_amt::numeric AS al   -- provider avg Medicare allowed amt
  FROM public.medicare_physician_service
  WHERE data_year = (SELECT yr FROM latest)
    AND hcpcs_drug_indicator = false               -- procedures only, not drugs
    AND avg_submitted_charge IS NOT NULL
    AND avg_medicare_allowed_amt IS NOT NULL
),
per_cell AS (
  SELECT
    hcpcs_code,
    place_of_service,
    max(hcpcs_description)                                          AS descr,
    count(*)                                                        AS providers,
    round(percentile_cont(0.1) WITHIN GROUP (ORDER BY sc)::numeric) AS p10,
    round(percentile_cont(0.5) WITHIN GROUP (ORDER BY sc)::numeric) AS median_charge,
    round(percentile_cont(0.9) WITHIN GROUP (ORDER BY sc)::numeric) AS p90,
    round((percentile_cont(0.9) WITHIN GROUP (ORDER BY sc)
          / nullif(percentile_cont(0.1) WITHIN GROUP (ORDER BY sc), 0))::numeric, 1)
                                                                    AS p90_p10_ratio,
    round((percentile_cont(0.5) WITHIN GROUP (ORDER BY sc)
          / nullif(percentile_cont(0.5) WITHIN GROUP (ORDER BY al), 0))::numeric, 1)
                                                                    AS charge_to_allowed
  FROM base
  GROUP BY hcpcs_code, place_of_service
),
hi_vol AS (                                  -- the 997-cell high-volume universe
  SELECT * FROM per_cell WHERE providers >= 1000
)

-- ============================================================================
-- (1) Headline: how wide is the spread across the high-volume universe?
--     The median cell's 90/10 charge ratio is the lead figure (3.60x).
-- ============================================================================
SELECT
  count(*)                                                               AS cells_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,
  count(*) FILTER (WHERE p90_p10_ratio >= 2)                             AS cells_ge_2x,
  count(*) FILTER (WHERE p90_p10_ratio >= 3)                             AS cells_ge_3x,
  count(*) FILTER (WHERE p90_p10_ratio >= 5)                             AS cells_ge_5x
FROM hi_vol;
--  cells p25  median p75  ge_2x ge_3x ge_5x
--  997   2.91 3.60   4.60 995   715   199

-- ============================================================================
-- (2) The spread on the most COMMON services — same code + setting, priced
--     across providers. (Ranked by provider count; the study's first table.)
-- ============================================================================
SELECT hcpcs_code, place_of_service, descr, providers,
       p10, median_charge, p90, p90_p10_ratio, charge_to_allowed
FROM hi_vol
WHERE (hcpcs_code, place_of_service) IN
      (('99214','O'),('99213','O'),('99204','O'),('99223','F'),('99215','O'),
       ('G0439','O'),('93000','O'),('70450','F'),('66984','F'))
ORDER BY providers DESC;
--  99214 O Established office visit, moderate   450,513  160   255   416  2.6  2.1
--  99213 O Established office visit, low        420,108  110   178   295  2.7  2.1
--  99204 O New office visit, moderate           210,166  239   368   585  2.4  2.3
--  99223 F Initial hospital care, high          140,863  316   497   915  2.9  3.0
--  99215 O Established office visit, high        127,124  225   379   637  2.8  2.2
--  G0439 O Annual wellness visit, subsequent     106,701  163   260   407  2.5  2.1
--  93000 O Routine ECG, 12-lead                   68,322   32    58   106  3.4  4.2
--  70450 F CT scan, head/brain, no contrast       19,414  123   182   485  4.0  4.8
--  66984 F Cataract removal w/ lens               10,370  970  2000  4515  4.7  3.8

-- ============================================================================
-- (3) The WIDEST-variation procedures — same universe, ranked by 90/10 ratio,
--     restricted to non-drug CPT codes with a non-trivial 10th percentile
--     (p10 >= 50) so a handful of nominal component-only charges cannot blow
--     up the ratio.
-- ============================================================================
SELECT hcpcs_code, place_of_service, descr, providers, p10, median_charge, p90, p90_p10_ratio
FROM hi_vol
WHERE hcpcs_code ~ '^[0-9]'        -- numeric CPT (drops misc/unclassified "J"-style codes)
  AND p10 >= 50
ORDER BY p90_p10_ratio DESC
LIMIT 8;
--  29828 F Biceps tenodesis, arthroscopic           1,081   787  3103  16000  20.3
--  29824 F Distal clavicle excision, arthroscopic   1,247   660  2313  11175  16.9
--  29823 F Shoulder debridement, arthroscopic       1,715   578  2287   9585  16.6
--  27130 F Hip replacement, total                   8,164  1007  4212  16370  16.3
--  29827 F Rotator cuff repair, arthroscopic        3,374  1000  3575  15782  15.8
--  63048 F Spine laminectomy, additional segment    2,722   213   740   3298  15.5
--  23472 F Total shoulder replacement               4,023  1092  4440  16513  15.1
--  29826 F Shoulder decompression, arthroscopic     2,425   261  1150   3879  14.9

-- ============================================================================
-- (3b) Device-inclusive implant codes run higher still — the device
--      acquisition cost is folded into the submitted charge.
-- ============================================================================
SELECT hcpcs_code, place_of_service, descr, providers, p10, median_charge, p90, p90_p10_ratio
FROM hi_vol
WHERE hcpcs_code IN ('63685','33285')
ORDER BY p90_p10_ratio DESC;
--  33285 F Insertion of cardiac rhythm monitor        1,424  188  345  17673  93.9
--  63685 F Spinal neurostimulator generator/receiver  1,202  796 2243  72203  90.7

-- ============================================================================
-- (4) The OTHER gap — national submitted-charge-to-Medicare-allowed multiple
--     (a different axis: charge-vs-allowed, service-weighted across every
--     non-drug Part B line). Included to separate it from the provider-to-
--     provider spread above.
-- ============================================================================
SELECT
  round(sum(avg_submitted_charge   * total_services)
      / nullif(sum(avg_medicare_allowed_amt * total_services), 0), 2) AS sw_submitted_to_allowed,
  round(sum(avg_medicare_allowed_amt * total_services)
      / nullif(sum(avg_medicare_payment_amt * total_services), 0), 2) AS sw_allowed_to_paid,
  count(DISTINCT npi)                                                 AS providers,
  round(sum(total_services))                                          AS services,
  round(sum(avg_submitted_charge    * total_services) / 1e9, 1)       AS submitted_billions,
  round(sum(avg_medicare_allowed_amt * total_services) / 1e9, 1)      AS allowed_billions
FROM public.medicare_physician_service
WHERE data_year = (SELECT yr FROM latest)
  AND hcpcs_drug_indicator = false
  AND avg_submitted_charge IS NOT NULL
  AND avg_medicare_allowed_amt IS NOT NULL;
--  sw_submitted_to_allowed sw_allowed_to_paid providers  services       submitted_B allowed_B
--  3.41                    1.28               1,207,305  1,605,743,071  352.2       103.1
