-- Who prescribes Medicare's drug spending? Nurse practitioners now lead
-- Fonteum reproducible query — https://fonteum.com/research/medicare-part-d-prescribing-by-specialty
-- Data as of: 2026-04-04  ·  methodology: part-d-specialty/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 specialty, 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_specialty_mv (one row per prescriber_specialty) +
--           part_d_prescribing_overview_mv (program totals), from
--           supabase/migrations/20260612150000_part_d_prescribing_research_views.sql.
--           The view is GROUP BY prescriber_specialty over the 2024 base rows,
--           summing total_claims and total_drug_cost. Claims are attributed to the
--           prescriber who wrote them, by self-reported CMS specialty.

-- 1. Top prescriber specialties by total drug cost:
SELECT specialty, claims, cost,
       round(cost::numeric / claims, 2) AS cost_per_claim
FROM public.part_d_by_specialty_mv
ORDER BY cost DESC
LIMIT 10;
--  Nurse Practitioner    269,894,701  $37,476,377,293  $138.86  <- highest total cost
--  Internal Medicine     332,609,911  $33,550,092,522  $100.87
--  Family Practice       371,050,213  $29,174,414,333  $78.63   <- most claims, 3rd in cost
--  Hematology-Oncology     7,083,076  $17,288,931,026  $2,440.88 <- highest cost per claim
--  Physician Assistant    95,064,409  $13,558,946,453  $142.63
--  Cardiology             64,689,193  $13,493,559,914  $208.59
--  Pulmonary Disease      13,915,148  $8,399,625,859   $603.63
--  Rheumatology           10,797,149  $8,398,839,294   $777.88
--  Neurology              20,245,985  $7,986,867,428   $394.49
--  Endocrinology          17,972,894  $7,969,575,863   $443.42

-- 2. Specialties ranked by cost PER CLAIM (the cost concentration):
SELECT specialty, claims, cost,
       round(cost::numeric / claims, 2) AS cost_per_claim
FROM public.part_d_by_specialty_mv
WHERE claims >= 1000000          -- exclude tiny subspecialties for a fair per-claim view
ORDER BY cost_per_claim DESC
LIMIT 10;
--  Hematology-Oncology  $2,440.88 ; Medical Oncology $2,394.05 ; Infectious Disease $1,005.30
--  Rheumatology $777.88 ; Pulmonary Disease $603.63 ; Endocrinology $443.42 ...
--  Family Practice $78.63 sits near the bottom — half the $153.24 program average.

-- 3. Primary-care share (NP + Internal Medicine + Family Practice):
WITH pc AS (
  SELECT sum(claims) AS claims, sum(cost) AS cost
  FROM public.part_d_by_specialty_mv
  WHERE specialty IN ('Nurse Practitioner','Internal Medicine','Family Practice')
)
SELECT pc.claims, pc.cost,
       round(100.0 * pc.cost   / ov.total_drug_cost, 1) AS pc_cost_pct,   -- 44.2%
       round(100.0 * pc.claims / ov.total_claims,    1) AS pc_claims_pct  -- 65.8%
FROM pc, public.part_d_prescribing_overview_mv ov;
-- 182 distinct prescriber specialties report in the 2024 file:
SELECT count(*) AS n_specialties FROM public.part_d_by_specialty_mv;   -- 182
