-- Which medical specialties take the most industry money?
-- Fonteum reproducible query — https://fonteum.com/research/open-payments-top-paid-specialties-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).

-- Which medical specialties take the most industry money? — 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_specialty (CMS pipe-delimited provider taxonomy). No recipient named.
--
-- Reads open_payments_by_specialty_mv; definition reproduced for audit.

-- Top 15 specialties by general-payment dollars (open_payments_by_specialty_mv):
SELECT
  recipient_specialty                  AS specialty,
  count(*)                             AS payments,
  round(sum(total_amount_usd))::bigint AS total_usd
FROM public.cms_open_payments
WHERE record_type = 'general' AND program_year = 2024 AND recipient_specialty IS NOT NULL
GROUP BY recipient_specialty
ORDER BY total_usd DESC
LIMIT 15;
--  ...|Orthopaedic Surgery                         381,416,269   222,891  <- highest specialty; avg $1,711
--  ...|Internal Medicine                           113,403,390 1,182,047  <- avg $96
--  Dental Providers|Dentist|Endodontics            102,330,286     6,127  <- ~$91.1M from one acquisition
--  ...|Psychiatry & Neurology|Neurology             89,611,200   459,825
--  ...|Dermatology                                  89,106,518   521,576
--  ...|Neurological Surgery                          83,073,789    83,869  <- avg $991
--  ...|Internal Medicine|Cardiovascular Disease      71,590,219   452,186
--  ...|Internal Medicine|Hematology & Oncology       68,550,441   331,168
--  ...|Psychiatry & Neurology|Psychiatry             66,292,782   317,401
--  ...|Internal Medicine|Gastroenterology            65,513,271   455,469
--  ...|Surgery                                       65,168,098   175,944
--  ...|Ophthalmology                                 60,804,258   198,993
--  Physician Assistant                               58,785,970 1,386,673  <- avg $42
--  Nurse Practitioner|Family                         58,448,657 1,492,157  <- avg $39
--  ...|Orthopaedic Surgery of the Spine              54,760,634    32,616

-- Orthopedic-surgery family total (primary + the three subspecialties shown above):
SELECT round(sum(total_amount_usd))::bigint AS ortho_family_usd                   -- 531,816,444
FROM public.cms_open_payments
WHERE record_type = 'general' AND program_year = 2024
  AND recipient_specialty IN (
    'Allopathic & Osteopathic Physicians|Orthopaedic Surgery',
    'Allopathic & Osteopathic Physicians|Orthopaedic Surgery|Orthopaedic Surgery of the Spine',
    'Allopathic & Osteopathic Physicians|Orthopaedic Surgery|Adult Reconstructive Orthopaedic Surgery',
    'Allopathic & Osteopathic Physicians|Orthopaedic Surgery|Sports Medicine'
  );

-- Intensity contrast — average payment, orthopedics vs internal medicine:
SELECT
  recipient_specialty                               AS specialty,
  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_specialty IN (
    'Allopathic & Osteopathic Physicians|Orthopaedic Surgery',     -- 1,711
    'Allopathic & Osteopathic Physicians|Internal Medicine'        -- 96
  )
GROUP BY recipient_specialty;
