-- GLP-1 drugs now cost Medicare Part D $24.57 billion a year
-- Fonteum reproducible query — https://fonteum.com/research/medicare-part-d-glp1-spending
-- Data as of: 2026-04-04  ·  methodology: part-d-glp1/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).

-- GLP-1 drugs in Medicare Part D, 2024 — fully 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 five frozen materialized views created by
--           supabase/migrations/20260612150000_part_d_prescribing_research_views.sql.
--           These views aggregate the 28M-row base table ONCE (the base table is
--           too large to GROUP BY inside an 8s request), so every figure below is
--           bounded and reproducible. The GLP-1 slice is computed in the overview
--           view as a FILTER over generic_name; the per-brand rows are read from
--           the costliest-drugs view.
--
-- Every headline figure in the study resolves to one of the rows below.

-- 1. Program totals + the GLP-1 class slice (single overview row):
SELECT
  total_drug_cost,                                              -- $226,740,902,131  program total
  total_claims,                                                 -- 1,479,628,807     program claims
  glp1_cost,                                                    -- $24,567,586,107   GLP-1 class cost
  glp1_claims,                                                  -- 19,591,489        GLP-1 class claims
  round(100.0 * glp1_cost   / total_drug_cost, 2) AS glp1_cost_pct,    -- 10.84%
  round(100.0 * glp1_claims / total_claims,    2) AS glp1_claims_pct,  --  1.32%
  round(glp1_cost::numeric / glp1_claims, 2)      AS glp1_cost_per_claim,   -- $1,253.99
  round(total_drug_cost::numeric / total_claims, 2) AS program_cost_per_claim -- $153.24
FROM public.part_d_prescribing_overview_mv;

-- The GLP-1 class is defined exactly as in the view: generic_name in
--   ('Semaglutide','Tirzepatide','Dulaglutide','Liraglutide',
--    'Exenatide','Exenatide Microspheres','Lixisenatide','Semaglutide (Oral)')
-- (the GLP-1 receptor agonists), summed over data_year = 2024.

-- 2. The GLP-1 brands that surface in the 50 costliest Part D drugs:
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
WHERE generic_name IN ('Semaglutide','Tirzepatide','Dulaglutide','Liraglutide',
                       'Exenatide','Exenatide Microspheres','Lixisenatide','Semaglutide (Oral)')
ORDER BY cost DESC;
--  2  Ozempic    Semaglutide        9,930,484  $12,375,986,475  $1,246  <- 2nd costliest drug overall
--  4  Mounjaro   Tirzepatide        4,694,896  $5,825,295,741   $1,241
--  7  Trulicity  Dulaglutide        3,816,376  $4,786,817,612   $1,254
-- 33  Rybelsus   Semaglutide (Oral)   899,704  $1,309,259,751   $1,455
-- The four named brands = $24,297,359,579 = 98.9% of the $24.57B class total;
-- the remaining 1.1% is older GLP-1s (Victoza, Byetta, etc.) below the top 50.

-- 3. Wegovy / Zepbound absence check — neither appears in the 50 costliest drugs
--    (Part D excludes weight-loss-only agents per SSA §1860D-2(e)(2)):
SELECT count(*) AS weight_loss_glp1_in_top50
FROM public.part_d_top_drugs_by_cost_mv
WHERE brand_name IN ('Wegovy','Zepbound');     -- 0
