-- GLP-1 makers paid 120,237 Medicare prescribers $32.8 million in 2024 — and the prescribers they paid wrote far more
-- Fonteum reproducible query — https://fonteum.com/research/glp1-pharma-payments-to-prescribers-2024
-- Data as of: 2026-06-12  ·  methodology: glp1-payments-prescribing/v1
-- Source datasets: CMS Open Payments, 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 drug-maker payments vs GLP-1 prescribing, 2024 — fully reproducible cross-dataset join.
--
-- The angle no one else can publish: join the money (CMS Open Payments) to the
-- prescribing (CMS Medicare Part D Prescribers) on the SAME federal NPI, for the
-- GLP-1 drug class only, and measure how manufacturer payments line up with
-- prescribing volume — at the aggregate cohort level, never per physician.
--
-- Sources (both public, US-Government-Works):
--   public.cms_open_payments         PY2024  (Open Payments / Sunshine Act, ~16.2M records)
--   public.cms_part_d_prescribers    DY2024  (Part D Prescribers by Provider and Drug, ~28.0M rows)
-- Join key: cms_open_payments.recipient_npi = cms_part_d_prescribers.prescriber_npi.
-- Capture date: 2026-06-12 (read-only against the live tables; no view, no migration).
--
-- GLP-1 makers = Novo Nordisk + Eli Lilly, the two firms that make essentially the
-- entire modern GLP-1 market. Their manufacturer_id values were read directly from
-- the table (a LIMIT-bounded scan short-circuits on the indexed product match):
--   100000000163  Novo Nordisk AS    (Ozempic, Wegovy, Rybelsus, Victoza, Saxenda)
--   100000000144  Novo Nordisk Inc   (Victoza and earlier filings)
--   100000000066  Lilly USA, LLC     (Mounjaro, Trulicity, Zepbound)
-- A "GLP-1-product payment" is a payment whose product_name_1 names a GLP-1 brand.
-- We count GENERAL payments to prescribers (physicians + non-physician practitioners),
-- the standard "payments to prescribers" definition; research grants (which flow to
-- institutions and trial sites, not to prescribers as marketing) are reported and
-- excluded separately in query 4.
-- Every headline figure in the study resolves to one of the rows below.

-- 1. GLP-1-product GENERAL payments to prescribers, by maker and by nature of payment.
--    Uses the manufacturer_id index, so the scan is bounded to Novo + Lilly rows.
WITH gp AS (
  SELECT
    CASE WHEN manufacturer_id IN ('100000000163','100000000144') THEN 'Novo Nordisk'
         ELSE 'Eli Lilly' END                 AS maker,
    nature_of_payment, recipient_npi, total_amount_usd, number_of_payments
  FROM public.cms_open_payments
  WHERE program_year = 2024
    AND record_type  = 'general'
    AND recipient_type IN ('Covered Recipient Physician',
                           'Covered Recipient Non-Physician Practitioner')
    AND manufacturer_id IN ('100000000163','100000000144','100000000066')
    AND (product_name_1 ILIKE 'ozempic%'  OR product_name_1 ILIKE 'wegovy%'
      OR product_name_1 ILIKE 'rybelsus%' OR product_name_1 ILIKE 'victoza%'
      OR product_name_1 ILIKE 'saxenda%'  OR product_name_1 ILIKE 'mounjaro%'
      OR product_name_1 ILIKE 'trulicity%' OR product_name_1 ILIKE 'zepbound%')
)
SELECT 'TOTAL' AS grp,
       count(*) AS records, count(DISTINCT recipient_npi) AS recipients,
       round(sum(total_amount_usd)) AS dollars FROM gp
UNION ALL
SELECT 'maker:'||maker, count(*), count(DISTINCT recipient_npi), round(sum(total_amount_usd))
  FROM gp GROUP BY maker
UNION ALL
SELECT 'nature:'||nature_of_payment, count(*), count(DISTINCT recipient_npi), round(sum(total_amount_usd))
  FROM gp GROUP BY nature_of_payment
ORDER BY dollars DESC;
-- TOTAL                                       691,316 records  120,237 recipients  $32,766,256
-- nature: Compensation ... faculty or speaker  15,145 records      644 recipients  $17,883,925  (54.6% of $)
-- maker:  Eli Lilly                           265,012 records   72,642 recipients  $17,209,752
-- maker:  Novo Nordisk                        426,304 records   99,338 recipients  $15,556,504
-- nature: Food and Beverage                   668,858 records  119,810 recipients  $13,204,078  (96.8% of records; avg $19.74)
-- nature: Travel and Lodging                    4,805 records      517 recipients     $858,595
-- nature: Consulting Fee                          250 records      126 recipients     $739,050
-- nature: Education                             2,258 records    1,683 recipients      $80,609

-- 2. THE JOIN — GLP-1 prescribing for prescribers who DID vs DID NOT receive a
--    GLP-1-product general payment. glp1_rx (Part D, GLP-1 generics) and glp1_pay
--    (the maker payments above, one row per recipient NPI) are both small, so the
--    LEFT JOIN on NPI is a cheap hash join. Cohort sums give the universe totals.
WITH glp1_rx AS (
  SELECT prescriber_npi AS npi, sum(total_claims) AS claims, sum(total_drug_cost) AS cost
  FROM public.cms_part_d_prescribers
  WHERE data_year = 2024
    AND generic_name IN ('Semaglutide','Tirzepatide','Dulaglutide',
                         'Liraglutide','Exenatide','Lixisenatide','Albiglutide')
  GROUP BY prescriber_npi
),
glp1_pay AS (
  SELECT recipient_npi AS npi, sum(total_amount_usd) AS pay
  FROM public.cms_open_payments
  WHERE program_year = 2024
    AND record_type  = 'general'
    AND recipient_type IN ('Covered Recipient Physician',
                           'Covered Recipient Non-Physician Practitioner')
    AND manufacturer_id IN ('100000000163','100000000144','100000000066')
    AND (product_name_1 ILIKE 'ozempic%'  OR product_name_1 ILIKE 'wegovy%'
      OR product_name_1 ILIKE 'rybelsus%' OR product_name_1 ILIKE 'victoza%'
      OR product_name_1 ILIKE 'saxenda%'  OR product_name_1 ILIKE 'mounjaro%'
      OR product_name_1 ILIKE 'trulicity%' OR product_name_1 ILIKE 'zepbound%')
  GROUP BY recipient_npi
)
SELECT
  CASE WHEN p.npi IS NOT NULL THEN 'received_glp1_payment' ELSE 'no_glp1_payment' END AS cohort,
  count(*)                                              AS prescribers,
  sum(r.claims)                                         AS glp1_claims,
  round(sum(r.cost))                                    AS glp1_cost,
  round(avg(r.claims), 1)                               AS avg_claims,
  round(avg(r.cost))                                    AS avg_cost,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY r.claims) AS median_claims
FROM glp1_rx r
LEFT JOIN glp1_pay p ON p.npi = r.npi
GROUP BY 1;
-- received_glp1_payment   75,905 prescribers  10,195,741 claims  $13,079,331,733  avg 134.3  avg $172,312  median 82
-- no_glp1_payment        123,710 prescribers   9,310,328 claims  $11,398,976,526  avg  75.3  avg  $92,143  median 44
--
-- Universe (cohort sums):  199,615 GLP-1 prescribers · 19,506,069 claims · $24,478,308,259 cost.
-- Paid cohort = 75,905 / 199,615 = 38.0% of prescribers, yet
--   52.3% of claims (10,195,741 / 19,506,069) and 53.4% of cost ($13.08B / $24.48B).
-- Mean ratio 134.3 / 75.3 = 1.78x (78% more);  median ratio 82 / 44 = 1.86x.

-- 3. Dose-response gradient — average GLP-1 prescribing by payment tier received.
--    Same two CTEs as query 2; only the final grouping differs.
WITH glp1_rx AS (
  SELECT prescriber_npi AS npi, sum(total_claims) AS claims, sum(total_drug_cost) AS cost
  FROM public.cms_part_d_prescribers
  WHERE data_year = 2024
    AND generic_name IN ('Semaglutide','Tirzepatide','Dulaglutide',
                         'Liraglutide','Exenatide','Lixisenatide','Albiglutide')
  GROUP BY prescriber_npi
),
glp1_pay AS (
  SELECT recipient_npi AS npi, sum(total_amount_usd) AS pay
  FROM public.cms_open_payments
  WHERE program_year = 2024
    AND record_type  = 'general'
    AND recipient_type IN ('Covered Recipient Physician',
                           'Covered Recipient Non-Physician Practitioner')
    AND manufacturer_id IN ('100000000163','100000000144','100000000066')
    AND (product_name_1 ILIKE 'ozempic%'  OR product_name_1 ILIKE 'wegovy%'
      OR product_name_1 ILIKE 'rybelsus%' OR product_name_1 ILIKE 'victoza%'
      OR product_name_1 ILIKE 'saxenda%'  OR product_name_1 ILIKE 'mounjaro%'
      OR product_name_1 ILIKE 'trulicity%' OR product_name_1 ILIKE 'zepbound%')
  GROUP BY recipient_npi
)
SELECT
  CASE WHEN p.pay IS NULL THEN '0 none'
       WHEN p.pay < 100   THEN '1 $1-99'
       WHEN p.pay < 1000  THEN '2 $100-999'
       ELSE                    '3 $1,000+' END           AS pay_tier,
  count(*)                                               AS prescribers,
  round(avg(r.claims), 1)                                AS avg_glp1_claims,
  round(avg(r.cost))                                     AS avg_glp1_cost,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY r.claims)  AS median_claims
FROM glp1_rx r
LEFT JOIN glp1_pay p ON p.npi = r.npi
GROUP BY 1 ORDER BY 1;
-- 0 none        123,710 prescribers  avg  75.3 claims  avg  $92,143  median  44
-- 1 $1-99        40,665 prescribers  avg 105.9 claims  avg $132,547  median  66
-- 2 $100-999     34,621 prescribers  avg 160.0 claims  avg $207,992  median 102
-- 3 $1,000+         619 prescribers  avg 564.6 claims  avg $789,049  median 409
-- The 619 prescribers paid $1,000+ averaged 564.6 GLP-1 claims — 7.5x the 75.3 of
-- prescribers who took no GLP-1-product payment. Monotonic, and strictly correlational.

-- 4. Research payments — reported and excluded from the prescriber-marketing total above.
--    GLP-1-product RESEARCH payments flow overwhelmingly to entities and teaching
--    hospitals (trial sites), not to prescribers, so they are not part of the
--    "payments to prescribers" measure.
SELECT record_type,
       count(*) AS records, count(DISTINCT recipient_npi) AS recipients,
       round(sum(total_amount_usd)) AS dollars
FROM public.cms_open_payments
WHERE program_year = 2024
  AND manufacturer_id IN ('100000000163','100000000144','100000000066')
  AND (product_name_1 ILIKE 'ozempic%'  OR product_name_1 ILIKE 'wegovy%'
    OR product_name_1 ILIKE 'rybelsus%' OR product_name_1 ILIKE 'victoza%'
    OR product_name_1 ILIKE 'saxenda%'  OR product_name_1 ILIKE 'mounjaro%'
    OR product_name_1 ILIKE 'trulicity%' OR product_name_1 ILIKE 'zepbound%')
GROUP BY record_type ORDER BY dollars DESC;
-- research   36,187 records  (to entities / teaching hospitals / 51 physicians)  $33,639,331
-- general   691,325 records                                                      $32,783,306
-- (The general row here is slightly larger than query 1's $32,766,256 because it
--  also includes the few teaching-hospital general payments that query 1's
--  recipient_type filter excludes; the prescriber-marketing figure is query 1.)
