-- The few drugs that drive most of Medicare's Part D bill, 2023
-- Fonteum reproducible query — https://fonteum.com/research/medicare-part-d-spending-concentration-2023
-- Data as of: 2026-06-16  ·  methodology: cms-part-d-spending/v1
-- Source datasets: CMS Provider Data Catalog
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- How concentrated is Medicare Part D drug spending — and are the prices of the
-- drugs that dominate the bill still rising? Fully reproducible query.
--
-- Question: across the drugs Medicare Part D pays for, how much of the program's
-- total drug bill is carried by a handful of products, who are they, and what is
-- happening to their per-unit prices? The lead figure: the 100 costliest drugs —
-- 2.8% of the 3,598 drugs on CMS's published spending file — account for 63.8%
-- of the $275.9B Part D drug bill ($176.2B). These are spending facts over a
-- published drug-level file, NOT a quality, fraud, or pricing-fairness judgment.
--
-- Source:
--   public.cms_part_d_spending_by_drug — CMS "Medicare Part D Spending by Drug"
--     public-use file, published annually via data.cms.gov (Summary Statistics
--     on Use and Payments / Medicare-Medicaid Spending by Drug). Data year 2023;
--     source release 2025-05-19. Public, read-only. License: US-Government-Works
--     (17 U.S.C. Sec. 105). methodology_version = 'cms-part-d-spending/v1'.
--
-- Universe + double-count guard: the file ships TWO kinds of rows. An "Overall"
--   row (manufacturer_name = 'Overall') gives the all-manufacturer total for each
--   drug; additional rows break that total out by manufacturer. Summing every row
--   double-counts. This study reads the 3,598 "Overall" rows ONLY — one per drug —
--   which is the published Part D total. The other 10,711 of the 14,309 rows are
--   manufacturer breakouts and are excluded from every aggregate below.
--
-- Grain: each row is a drug (brand x generic), not a provider. No NPI, no
--   beneficiary, no prescriber is present or named anywhere in this study.

-- ============================================================================
-- (1) Universe reconciliation — the published file at a glance, Overall rows only.
-- ============================================================================
SELECT
  count(*)                                                           AS drugs,
  count(*) FILTER (WHERE manufacturer_name <> 'Overall')             AS mfr_breakout_rows_excluded,
  (SELECT count(*) FROM public.cms_part_d_spending_by_drug)          AS rows_in_file,
  sum(total_spending)::numeric::bigint                               AS total_spend,
  max(data_year)                                                     AS data_year,
  max(source_release_date)                                           AS source_release
FROM public.cms_part_d_spending_by_drug
WHERE manufacturer_name = 'Overall';
--  drugs 3,598 · mfr_breakout_rows_excluded 10,711 · rows_in_file 14,309
--  total_spend 275,924,520,551 ($275.9B) · data_year 2023 · source_release 2025-05-19

-- ============================================================================
-- (2) HEADLINE: spend concentration. Rank drugs by total spend and read the
--     cumulative share. 10 drugs = a quarter of the bill; 50 = half; 100 = 63.8%.
-- ============================================================================
WITH ranked AS (
  SELECT total_spending,
    row_number() OVER (ORDER BY total_spending DESC)                 AS rn,
    sum(total_spending) OVER ()                                      AS grand
  FROM public.cms_part_d_spending_by_drug
  WHERE manufacturer_name = 'Overall'
)
SELECT
  round(100.0 * sum(total_spending) FILTER (WHERE rn <= 10)  / max(grand), 1) AS top10_pct,
  round(100.0 * sum(total_spending) FILTER (WHERE rn <= 25)  / max(grand), 1) AS top25_pct,
  round(100.0 * sum(total_spending) FILTER (WHERE rn <= 50)  / max(grand), 1) AS top50_pct,
  round(100.0 * sum(total_spending) FILTER (WHERE rn <= 100) / max(grand), 1) AS top100_pct,
  round(100.0 * sum(total_spending) FILTER (WHERE rn <= 250) / max(grand), 1) AS top250_pct,
  (sum(total_spending) FILTER (WHERE rn <= 100))::numeric::bigint            AS top100_spend
FROM ranked;
--  top10 25.8% · top25 38.9% · top50 50.7% · top100 63.8% · top250 80.5%
--  top100_spend 176,157,336,695 ($176.2B). 100 of 3,598 drugs = 2.8% of the list.

-- ============================================================================
-- (3) The top of the list — the 10 costliest drugs and their share of the bill.
--     Seven are brand-name diabetes / anticoagulant / respiratory treatments;
--     two (Humira, Revlimid) are specialty drugs reaching far fewer beneficiaries.
-- ============================================================================
WITH o AS (
  SELECT *, sum(total_spending) OVER () AS grand
  FROM public.cms_part_d_spending_by_drug WHERE manufacturer_name = 'Overall'
)
SELECT
  brand_name, generic_name,
  total_spending::numeric::bigint                                    AS spend,
  round(100.0 * total_spending / grand, 1)                          AS pct_of_total,
  total_beneficiaries                                               AS beneficiaries,
  round(avg_spend_per_beneficiary)::bigint                          AS spend_per_bene
FROM o
ORDER BY total_spending DESC
LIMIT 10;
--  Eliquis    Apixaban       $18.27B 6.6% 3,927,848 benes $4,652
--  Ozempic    Semaglutide    $9.19B  3.3% 1,464,468 benes $6,278
--  Jardiance  Empagliflozin  $8.84B  3.2% 1,882,768 benes $4,695
--  Trulicity  Dulaglutide    $7.36B  2.7%   938,731 benes $7,844
--  Xarelto    Rivaroxaban    $6.31B  2.3% 1,324,165 benes $4,765
--  Trelegy Ellipta           $4.46B  1.6% 1,050,583 benes $4,241
--  Humira(Cf) Pen Adalimumab $4.42B  1.6%    61,474 benes $71,898  <- few patients, huge bill
--  Farxiga    Dapagliflozin  $4.34B  1.6%   993,909 benes $4,369
--  Januvia    Sitagliptin    $4.09B  1.5%   843,391 benes $4,850
--  Revlimid   Lenalidomide   $3.86B  1.4%    36,967 benes $104,412 <- specialty cancer drug

-- ============================================================================
-- (4) The second road to a big bill — price per beneficiary, not volume. Among
--     drugs with >= $100M total spend, the highest per-beneficiary costs are
--     rare-disease therapies reaching only a few hundred patients each.
-- ============================================================================
SELECT
  brand_name, generic_name,
  total_spending::numeric::bigint                                    AS spend,
  total_beneficiaries                                               AS beneficiaries,
  round(avg_spend_per_beneficiary)::bigint                          AS spend_per_bene
FROM public.cms_part_d_spending_by_drug
WHERE manufacturer_name = 'Overall' AND total_spending >= 100000000
ORDER BY avg_spend_per_beneficiary DESC
LIMIT 5;
--  Strensiq  Asfotase Alfa   $258.9M    207 benes $1,250,918
--  Soliris   Eculizumab      $223.5M    462 benes   $483,709
--  Takhzyro  Lanadelumab     $194.4M    468 benes   $415,315
--  Firdapse  Amifampridine   $146.3M    356 benes   $411,048
--  Acthar    Corticotropin   $377.8M    942 benes   $401,104

-- ============================================================================
-- (5) Are prices rising? The file carries each drug's 1-year and 5-year change
--     in average spend per dosage unit. A majority of priced drugs rose on both
--     horizons. (Percentages use the non-null base; 208 drugs lack a 1-year
--     change and 175 lack a 5-year CAGR.)
-- ============================================================================
SELECT
  count(*) FILTER (WHERE change_avg_spend_per_dosage_unit IS NOT NULL)         AS yoy_base,
  count(*) FILTER (WHERE change_avg_spend_per_dosage_unit > 0)                 AS rose_yoy,
  round(100.0 * count(*) FILTER (WHERE change_avg_spend_per_dosage_unit > 0)
        / count(*) FILTER (WHERE change_avg_spend_per_dosage_unit IS NOT NULL), 1) AS rose_yoy_pct,
  count(*) FILTER (WHERE cagr_avg_spend_per_dosage_unit IS NOT NULL)           AS cagr_base,
  count(*) FILTER (WHERE cagr_avg_spend_per_dosage_unit > 0)                   AS cagr_pos,
  round(100.0 * count(*) FILTER (WHERE cagr_avg_spend_per_dosage_unit > 0)
        / count(*) FILTER (WHERE cagr_avg_spend_per_dosage_unit IS NOT NULL), 1) AS cagr_pos_pct
FROM public.cms_part_d_spending_by_drug
WHERE manufacturer_name = 'Overall';
--  yoy_base 3,390 · rose 2,084 · 61.5% rose 2022-2023
--  cagr_base 3,423 · positive 2,225 · 65.0% positive 5-yr CAGR (2019-2023)

-- ============================================================================
-- (6) The steepest 1-year per-unit price climbs among material drugs (>= $50M
--     spend), so the list is not dominated by tiny products with noisy ratios.
-- ============================================================================
SELECT
  brand_name, generic_name,
  total_spending::numeric::bigint                                    AS spend,
  round(100.0 * change_avg_spend_per_dosage_unit, 1)                AS yoy_unit_price_pct,
  change_period
FROM public.cms_part_d_spending_by_drug
WHERE manufacturer_name = 'Overall'
  AND total_spending >= 50000000
  AND change_avg_spend_per_dosage_unit IS NOT NULL
ORDER BY change_avg_spend_per_dosage_unit DESC
LIMIT 5;
--  Zejula   Niraparib   $161.5M  +43.1%  2022-2023
--  Entyvio  Vedolizumab $100.0M  +36.5%  2022-2023
--  Lumakras Sotorasib   $140.2M  +34.9%  2022-2023
--  Erleada  Apalutamide $1.06B   +32.1%  2022-2023
--  Metformin ER Gastric  $191.1M +27.3%  2022-2023

-- ============================================================================
-- (7) Why so concentrated — brand vs generic. The partition is
--     lower(brand_name) = lower(generic_name): a generic is published under its
--     own ingredient name. Generics are 601 of 3,598 drugs but only 9.4% of the
--     dollars; brand-name drugs carry 90.6% of the bill.
-- ============================================================================
SELECT
  count(*) FILTER (WHERE lower(brand_name) = lower(generic_name))             AS generic_drugs,
  (sum(total_spending) FILTER (WHERE lower(brand_name) = lower(generic_name)))::numeric::bigint AS generic_spend,
  round(100.0 * sum(total_spending) FILTER (WHERE lower(brand_name) = lower(generic_name))
        / sum(total_spending), 1)                                            AS generic_spend_pct
FROM public.cms_part_d_spending_by_drug
WHERE manufacturer_name = 'Overall';
--  generic_drugs 601 · generic_spend $25.83B · generic_spend_pct 9.4% (brand = 90.6%)
