-- The most expensive Medicare Part D drugs are rarely the most prescribed
-- Fonteum reproducible query — https://fonteum.com/research/most-expensive-medicare-part-d-drugs
-- Data as of: 2026-04-04  ·  methodology: part-d-costliest/v1
-- Source datasets: CMS Medicare Part D Prescribers
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Most expensive vs most-prescribed Medicare Part D drugs, 2024 — reproducible query.
--
-- Source:   CMS Medicare Part D Prescribers, "by Provider and Drug".
-- Snapshot: cms-part-d-prescribers / data year 2024 / CMS release 2026-04-04.
-- Base:     public.cms_part_d_prescribers (~28.0M prescriber × drug rows).
-- Reads:    the frozen materialized views from
--           supabase/migrations/20260612150000_part_d_prescribing_research_views.sql.
--           The two top-drug views are each GROUP BY brand_name, generic_name over
--           the 2024 base rows, ranked by cost and by claims respectively; the
--           overview view carries the brand-vs-generic partition.
--
-- The whole study is the contrast between two orderings of the same drugs.

-- 1. The 12 costliest drugs (the "price economy"):
SELECT rank, brand_name, generic_name, claims, cost,
       round(cost::numeric / claims, 2) AS cost_per_claim
FROM public.part_d_top_drugs_by_cost_mv
ORDER BY cost DESC
LIMIT 12;
--  1 Eliquis    Apixaban       22,996,543  $19,876,304,796  $864     <- costliest drug; 12th by claims
--  2 Ozempic    Semaglutide     9,930,484  $12,375,986,475  $1,246
--  3 Jardiance  Empagliflozin  10,654,599  $10,678,957,372  $1,002
--  4 Mounjaro   Tirzepatide     4,694,896  $5,825,295,741   $1,241
--  5 Xarelto    Rivaroxaban     5,888,827  $5,436,701,739   $923
--  6 Trelegy …                  5,725,949  $4,832,494,643   $844
--  7 Trulicity  Dulaglutide     3,816,376  $4,786,817,612   $1,254
--  9 Humira(Cf) Adalimumab        437,072  $3,795,159,532   $8,683   <- giant bill on few claims
-- 10 Revlimid   Lenalidomide      217,318  $3,788,009,700   $17,431  <- specialty cancer drug
-- 11 Vyndamax   Tafamidis          98,896  ~$2,500,000,000  $25,279  <- specialty heart drug
--    (Vyndamax cost ≈ $2.50B; cost_per_claim = cost / 98,896 claims = $25,279)

-- 2. The 12 most-prescribed drugs (the "volume economy"):
SELECT rank, brand_name, generic_name, claims, cost,
       round(cost::numeric / claims, 2) AS cost_per_claim,
       lower(brand_name) = lower(generic_name) AS is_generic
FROM public.part_d_top_drugs_by_claims_mv
ORDER BY claims DESC
LIMIT 12;
--  1 Atorvastatin Calcium  72,474,420  $748,890,955   $10.33  generic
--  2 Amlodipine Besylate   49,241,894  $310,756,194   $6.31   generic
--  3 Levothyroxine Sodium  42,465,073  $456,346,665   ...     generic
--  ...  (ranks 1-11 are ALL generic)
-- 12 Eliquis   Apixaban     22,996,543  $19,876,304,796        brand   <- first brand on the list

-- 3. The brand-vs-generic partition (the structural inversion):
SELECT
  generic_claims, brand_claims,
  generic_cost,   brand_cost,
  round(100.0 * generic_claims / total_claims,    1) AS generic_claims_pct,  -- 76.1%
  round(100.0 * generic_cost   / total_drug_cost, 1) AS generic_cost_pct,    --  9.9%
  round(100.0 * brand_claims   / total_claims,    1) AS brand_claims_pct,    -- 23.9%
  round(100.0 * brand_cost     / total_drug_cost, 1) AS brand_cost_pct       -- 90.1%
FROM public.part_d_prescribing_overview_mv;
-- generic + brand claims sum exactly to total_claims; generic + brand cost to
-- total_drug_cost — the partition is by lower(brand_name) = lower(generic_name).
