-- Medicare Part D drug spending by state: where the dollars concentrate
-- Fonteum reproducible query — https://fonteum.com/research/medicare-part-d-prescribing-by-state
-- Data as of: 2026-04-04  ·  methodology: part-d-geography/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).

-- Medicare Part D prescribing by state, 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:    part_d_by_state_mv (one row per prescriber_state) +
--           part_d_prescribing_overview_mv (program totals), from
--           supabase/migrations/20260612150000_part_d_prescribing_research_views.sql.
--           The view is GROUP BY prescriber_state over the 2024 base rows where
--           length(prescriber_state) = 2, summing total_claims and total_drug_cost.
--           State is the PRESCRIBER'S practice location, not the patient's home.

-- 1. Top states by total drug cost:
SELECT state, claims, cost,
       round(cost::numeric / claims, 2) AS cost_per_claim
FROM public.part_d_by_state_mv
ORDER BY cost DESC
LIMIT 10;
--  CA  129,565,592  $21,262,400,165  $164.11  <- highest total
--  NY   95,240,368  $18,174,131,281  $190.82  <- highest cost/claim among large states
--  FL  113,582,265  $16,838,937,918  $148.25
--  TX   98,638,359  $15,746,441,934  $159.64
--  PA   72,980,145  $11,128,347,140  $152.48
--  OH   62,294,899  $9,043,071,424   $145.17
--  NC   52,430,568  $8,138,215,348   $155.22
--  MI   49,623,865  $7,821,347,989   $157.61
--  IL   51,867,241  $7,536,982,736   $145.31
--  GA   47,568,589  $7,304,129,570   $153.55

-- 2. Four-state concentration (CA + NY + FL + TX):
WITH top4 AS (
  SELECT sum(cost) AS cost
  FROM public.part_d_by_state_mv
  WHERE state IN ('CA','NY','FL','TX')
)
SELECT top4.cost,
       round(100.0 * top4.cost / ov.total_drug_cost, 1) AS top4_cost_pct  -- 31.8%
FROM top4, public.part_d_prescribing_overview_mv ov;

-- 3. Cost-per-claim spread across large states (the figure):
SELECT state, round(cost::numeric / claims, 2) AS cost_per_claim
FROM public.part_d_by_state_mv
WHERE state IN ('NY','MA','NJ','CA','TX','OH','MO')
ORDER BY cost_per_claim DESC;
--  NY $190.82 ; MA $183.00 ; NJ $173.39 ; CA $164.11 ; TX $159.64 ; OH $145.17 ; MO $136.29
--  Program average = $153.24 (overview_mv.total_drug_cost / total_claims).

-- 61 reporting jurisdictions (50 states + DC + territories with a 2-letter code):
SELECT count(*) AS n_jurisdictions FROM public.part_d_by_state_mv;   -- 61
