-- Industry payments to physicians by state: where the money lands
-- Fonteum reproducible query — https://fonteum.com/research/open-payments-by-state-2024
-- Data as of: 2026-01-23  ·  methodology: open-payments/v1
-- Source datasets: CMS Open Payments
-- Run against the frozen federal snapshot to reproduce the published figures.
-- License: U.S. Government Works (federal sources; 17 U.S.C. §105).

-- Industry payments to physicians by state — reproducible query.
--
-- Source:   CMS Open Payments, program year 2024 (PGYR2024, published 2026-01-23).
-- Table:    public.cms_open_payments (16,146,544 records, public, read-only).
-- Scope:    General (non-research) payments only  (record_type = 'general').
-- Grain:    recipient_state (59 jurisdictions). No recipient named.
--
-- Reads open_payments_by_state_mv and open_payments_largest_general_mv.

-- Top states by general-payment dollars, with per-payment intensity:
SELECT
  recipient_state                                  AS state,
  count(*)                                         AS payments,
  round(sum(total_amount_usd))::bigint             AS total_usd,
  round(sum(total_amount_usd) / count(*), 0)       AS avg_per_payment
FROM public.cms_open_payments
WHERE record_type = 'general' AND program_year = 2024 AND recipient_state IS NOT NULL
GROUP BY recipient_state
ORDER BY total_usd DESC
LIMIT 7;
--  CA  334,476,958  1,370,072    244
--  FL  304,725,698  1,347,271    226
--  PA  303,312,778    665,965    455   <- royalty hub (BioNTech)
--  MA  225,083,292    218,332  1,031   <- highest intensity (Genentech / Takeda royalties)
--  TX  221,196,400  1,445,102    153   <- highest payment count, lowest intensity
--  NY  211,935,025  1,032,846    205
--  MO  120,120,501    326,555    368

-- Jurisdiction count:
SELECT count(DISTINCT recipient_state) AS jurisdictions                            -- 59
FROM public.cms_open_payments
WHERE record_type = 'general' AND program_year = 2024 AND recipient_state IS NOT NULL;

-- Royalty geography — the largest individual general payments and their states
-- (open_payments_largest_general_mv): BioNTech -> PA = $170.3M across ranks 2-4;
-- Genentech + Takeda -> MA ~ $76.7M across ranks 7-12.
SELECT rank, amount, nature, manufacturer, state
FROM public.open_payments_largest_general_mv
WHERE nature = 'Royalty or License'
ORDER BY rank
LIMIT 12;
--  2   88,596,339  Royalty or License  BioNTech SE   PA
--  3   53,413,600  Royalty or License  BioNTech SE   PA
--  4   28,269,974  Royalty or License  BioNTech SE   PA   (BioNTech->PA subtotal = 170,279,913)
--  7   16,066,655  Royalty or License  Takeda        MA
--  8   13,180,782  Royalty or License  Genentech     MA
--  9   12,477,310  Royalty or License  Genentech     MA
--  10  12,070,094  Royalty or License  Genentech     MA
--  11  11,794,348  Royalty or License  Genentech     MA
--  12  11,134,507  Royalty or License  Takeda        MA   (Genentech+Takeda->MA subtotal = 76,723,696)
