-- The 5% of prescribers behind half of Medicare's drug bill
-- Fonteum reproducible query — https://fonteum.com/research/medicare-part-d-prescriber-concentration-2024
-- Data as of: 2026-04-04  ·  methodology: part-d-concentration/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 prescriber CONCENTRATION — fully reproducible query.
--
-- Question: among the clinicians who prescribe to Medicare beneficiaries, how
-- concentrated is the drug spending? We measure, over the prescriber-NPI
-- universe, what share of all Part D drug cost flows to the top 0.1% / 1% / 5%
-- / 10% / 50% of prescribers, the Gini coefficient across prescribers, and the
-- median-vs-mean gap. This is the PRESCRIBER-side companion to the costliest-
-- drugs study (drug-side: 12 drugs vs the rest) and the recipient-side twin of
-- the Open Payments concentration study (where the top 1% of physician
-- recipients take 66% of industry money). Here the axis is how drug spending
-- lands on prescribers, not which drugs or which companies.
--
-- Source:
--   public.cms_part_d_prescribers — CMS Medicare Part D Prescribers
--     "by Provider and Drug", data year 2024 (CMS release 2026-04-04).
--     One row per prescriber x drug. 28,023,892 records; public, read-only. License: US-Government-Works (17 U.S.C. §105).
--
-- Suppression: CMS omits any prescriber-and-drug record with <= 10 claims for
--   patient privacy, so the public file's minimum claim count is 11. Every
--   figure below is computed over the published records; excluding the smallest
--   rows slightly UNDERSTATES the long tail (i.e. true concentration is, if
--   anything, marginally lower than reported — never higher).
--
-- Prescriber universe: records carrying a prescriber NPI. 1,139,455 prescribers
--   holding $226,740,902,131 in total drug cost (the same program total used by
--   the GLP-1 spending study). null_npi rows = 0.
--
-- These aggregates run server-side (direct SQL) — a count + percentile over the
-- 28M-row table exceeds PostgREST's 8s statement timeout, which is why the page
-- renders point-in-time figures from this frozen 2026-04-04 snapshot rather
-- than reading the table at request time.

-- Per-prescriber rollup reused by every query below.
WITH recip AS (
  SELECT prescriber_npi,
         sum(total_drug_cost)::numeric AS amt,
         max(prescriber_specialty)     AS spec
  FROM public.cms_part_d_prescribers
  WHERE data_year = 2024
    AND prescriber_npi IS NOT NULL
  GROUP BY prescriber_npi
)

-- ============================================================================
-- (1) Headline: cumulative share of drug cost by top band.
--     The top 5% share (53.5%) is the lead figure; top 1% (23.6%) the second.
-- ============================================================================
SELECT b.label,
       ceil(max(n) * b.frac)                                           AS prescribers_in_band,
       round(sum(amt) FILTER (WHERE rk <= ceil(n * b.frac)))           AS cum_dollars,
       round(sum(amt) FILTER (WHERE rk <= ceil(n * b.frac))
             / max(tot) * 100, 1)                                      AS cum_share_pct,
       round(min(amt) FILTER (WHERE rk <= ceil(n * b.frac)))           AS min_amount_in_band
FROM (
  SELECT amt,
         row_number() OVER (ORDER BY amt DESC) AS rk,
         count(*)     OVER ()                  AS n,
         sum(amt)     OVER ()                  AS tot
  FROM recip
) ranked
CROSS JOIN (VALUES ('Top 0.1%', 0.001), ('Top 1%', 0.01), ('Top 5%', 0.05),
                   ('Top 10%', 0.10), ('Top 25%', 0.25), ('Top 50%', 0.50),
                   ('All 100%', 1.0)) AS b(label, frac)
GROUP BY b.label, b.frac
ORDER BY b.frac;
--  Top 0.1%     1,140     12,946,341,077   5.7   7,324,098
--  Top 1%      11,395     53,451,754,286  23.6   2,658,615
--  Top 5%      56,973    121,234,049,376  53.5     956,543
--  Top 10%    113,946    161,777,228,961  71.3     534,990
--  Top 25%    284,864    211,745,804,146  93.4     139,640
--  Top 50%    569,728    225,834,479,959  99.6       9,406
--  All 100% 1,139,455    226,740,902,131 100.0           0
--  (bottom 50% therefore split 100.0 - 99.6 = 0.4% of the dollars,
--   i.e. 569,728 prescribers share $906,422,172.)

-- ============================================================================
-- (2) Distribution shape — median vs mean, percentiles, and the long tail.
-- ============================================================================
SELECT
  count(*)                                                        AS prescribers,
  round(avg(amt))                                                 AS mean_prescriber,
  round(percentile_cont(0.5)  WITHIN GROUP (ORDER BY amt))        AS median_prescriber,
  round(percentile_cont(0.9)  WITHIN GROUP (ORDER BY amt))        AS p90_prescriber,
  round(percentile_cont(0.99) WITHIN GROUP (ORDER BY amt))        AS p99_prescriber,
  round(max(amt))                                                 AS max_prescriber,
  count(*) FILTER (WHERE amt >= 10000000)                         AS prescribers_ge_10m,
  count(*) FILTER (WHERE amt >= 1000000)                          AS prescribers_ge_1m,
  count(*) FILTER (WHERE amt <  100000)                           AS prescribers_lt_100k
FROM recip;
--  prescribers 1,139,455 · mean $198,991 · median $9,406 · p90 $534,987
--    · p99 $2,658,595 · max $108,716,019 (one reported prescriber NPI)
--  >=$10M 446 · >=$1M 53,573 · <$100k 815,543

-- ============================================================================
-- (3) Gini coefficient across prescribers + bottom-50% share.
--     Gini = (2 * sum(i * amt) / (n * sum(amt))) - (n + 1) / n,
--     with i = ascending rank. 0 = perfectly equal, 1 = one prescriber takes all.
-- ============================================================================
WITH ord AS (
  SELECT amt, row_number() OVER (ORDER BY amt) AS i FROM recip
),
agg AS (
  SELECT count(*)::numeric AS n,
         sum(amt)          AS tot,
         sum(i * amt)      AS wsum,
         sum(amt) FILTER (WHERE i <= (SELECT count(*) FROM recip) * 0.5) AS bottom50
  FROM ord
)
SELECT round((2.0 * wsum / (n * tot) - (n + 1.0) / n)::numeric, 3) AS gini,
       round((bottom50 / tot * 100)::numeric, 2)                  AS bottom50_share_pct
FROM agg;
--  gini 0.841 · bottom50_share 0.40   (US income Gini ≈ 0.41, US wealth ≈ 0.85)

-- ============================================================================
-- (4) Who sits in the top 1% — specialty mix of the 11,395 highest-cost
--     prescribers (reported specialty). No prescriber named.
-- ============================================================================
WITH ranked AS (
  SELECT amt, spec,
         row_number() OVER (ORDER BY amt DESC) AS rk,
         count(*)     OVER ()                  AS n
  FROM recip
),
top1 AS (
  SELECT amt, coalesce(nullif(trim(spec), ''), '(unreported)') AS specialty
  FROM ranked WHERE rk <= ceil(n * 0.01)
)
SELECT specialty,
       count(*)                                               AS prescribers_in_top1pct,
       round(sum(amt))                                        AS dollars,
       round(sum(amt) / (SELECT sum(amt) FROM top1) * 100, 1) AS pct_of_top1_dollars
FROM top1
GROUP BY specialty
ORDER BY dollars DESC
LIMIT 8;
--  Hematology-Oncology   2,446   11,624,439,069  21.7
--  Internal Medicine     1,243    5,549,338,116  10.4
--  Nurse Practitioner    1,211    5,441,850,916  10.2
--  Rheumatology            953    4,200,449,706   7.9
--  Medical Oncology        656    3,143,194,613   5.9
--  Pulmonary Disease       577    2,986,889,144   5.6
--  Endocrinology           714    2,830,146,488   5.3
--  Neurology               600    2,680,996,771   5.0
--  (oncology family — Hematology-Oncology + Medical Oncology — = 3,102
--   prescribers and 27.6% of all top-1% dollars.)

-- ============================================================================
-- (5) Universe reconciliation — totals over the published 2024 file.
-- ============================================================================
SELECT
  count(*)                                          AS total_rows,
  count(DISTINCT prescriber_npi)                    AS distinct_prescribers,
  round(sum(total_drug_cost))                       AS total_drug_cost,
  sum(total_claims)::bigint                         AS total_claims,
  min(total_claims)                                 AS min_claims,
  count(*) FILTER (WHERE prescriber_npi IS NULL)    AS null_npi_rows
FROM public.cms_part_d_prescribers
WHERE data_year = 2024;
--  total_rows 28,023,892 · distinct_prescribers 1,139,455
--  total_drug_cost 226,740,902,131 · total_claims 1,479,628,807
--  min_claims 11 (CMS suppresses <=10) · null_npi_rows 0
