-- The $8.5 billion nobody counts: research is most of industry's money
-- Fonteum reproducible query — https://fonteum.com/research/open-payments-research-concentration-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).

-- The $8.5 billion nobody counts — RESEARCH payments are the largest slice of
-- industry money in Open Payments, yet every prior analysis reads only the
-- general slice. Fully reproducible query.
--
-- Question: of the three kinds of payment the Sunshine Act discloses — general,
-- research, and ownership — where do the dollars actually sit, who receives
-- them, and how concentrated is the field that funds them? The lead figure:
-- research payments are $8.49 billion, 71.0% of all 2024 industry money, from
-- only 4.7% of the records — and 98.9% of those dollars go to research
-- institutions and teaching hospitals, not to individual physicians. These are
-- disclosure aggregates, NOT a quality, fraud, or wrongdoing signal of any kind.
-- No individual is named, ranked, or scored.
--
-- Source:
--   public.cms_open_payments — CMS Open Payments program-year-2024 release
--     (PGYR2024, published 2026-01-23 via openpaymentsdata.cms.gov). Public,
--     read-only. 16,146,544 records for program year 2024. License:
--     US-Government-Works (17 U.S.C. Sec. 105).
--     methodology_version = 'open-payments/v1'.
--
-- Universe: program year 2024, all three record types. "Research" = record_type
--   'research'; "general" = record_type 'general'; "ownership" = record_type
--   'ownership'. Figures are exact counts and sums over the published file, not
--   modeled. CMS publishes annually and restates prior years.

-- ============================================================================
-- (1) Universe — the three kinds of payment side by side. Research holds 71.0%
--     of the dollars on 4.7% of the records; its average payment is $11,222,
--     52x the $215 average general payment.
-- ============================================================================
WITH t AS (
  SELECT sum(total_amount_usd) AS all_usd, count(*) AS all_rec
  FROM public.cms_open_payments WHERE program_year = 2024
)
SELECT
  record_type,
  count(*)                                                          AS records,
  round(sum(total_amount_usd))                                      AS total_usd,
  round(100.0 * sum(total_amount_usd) / (SELECT all_usd FROM t), 1) AS pct_dollars,
  round(100.0 * count(*) / (SELECT all_rec FROM t), 1)             AS pct_records,
  round(sum(total_amount_usd) / count(*))                           AS avg_payment
FROM public.cms_open_payments
WHERE program_year = 2024
GROUP BY record_type
ORDER BY total_usd DESC;
--  research   756,906   $8,494,017,206   71.0%   4.7%   $11,222
--  general  15,385,047  $3,313,801,737   27.7%  95.3%      $215
--  ownership     4,591    $147,781,836    1.2%   0.0%   $32,189
--  (all three together = $11,955,600,779 across 16,146,544 records.)

-- ============================================================================
-- (2) WHO receives research money — by recipient type. 98.9% of research
--     dollars go to NON-covered entities (research sites, clinics, and other
--     organizations that are not themselves Open Payments "covered recipients")
--     and to teaching hospitals; only 1.1% reaches an individual physician or
--     non-physician practitioner. Research is an institutional money flow.
-- ============================================================================
SELECT
  recipient_type,
  count(*)                                                          AS records,
  round(sum(total_amount_usd))                                      AS total_usd,
  round(100.0 * sum(total_amount_usd)
        / sum(sum(total_amount_usd)) OVER (), 1)                   AS pct_of_research
FROM public.cms_open_payments
WHERE program_year = 2024 AND record_type = 'research'
GROUP BY recipient_type
ORDER BY total_usd DESC;
--  Non-covered Recipient Entity          $6,727,235,867   79.2%
--  Covered Recipient Teaching Hospital   $1,674,441,900   19.7%
--  Covered Recipient Physician              $91,476,016    1.1%
--  Covered Recipient Non-Physician Pract.      $713,451    0.0%
--  Non-covered Recipient Individual            $149,972    0.0%
--  institutions (entity + teaching hospital) = $8,401,677,767 = 98.91%
--  individuals (physician + NPP + individual) =    $92,339,439 =  1.09%

-- ============================================================================
-- (3) HOW CONCENTRATED the funders are. 683 manufacturers report research
--     payments; the top 10 account for 56.0% of every research dollar, the top
--     25 for 74.8%, the top 50 for 85.0%. The single largest (ModernaTX) is
--     7.8% on its own.
-- ============================================================================
WITH m AS (
  SELECT manufacturer_name, sum(total_amount_usd) AS usd
  FROM public.cms_open_payments
  WHERE program_year = 2024 AND record_type = 'research'
  GROUP BY manufacturer_name
),
tot AS (SELECT sum(usd) AS t, count(*) AS n FROM m),
ranked AS (SELECT usd, row_number() OVER (ORDER BY usd DESC) AS rk FROM m)
SELECT
  (SELECT n FROM tot)                                               AS distinct_funders,
  round((SELECT t FROM tot))                                        AS research_usd,
  round(100.0 * sum(usd) FILTER (WHERE rk = 1)  / (SELECT t FROM tot), 1) AS top1_pct,
  round(100.0 * sum(usd) FILTER (WHERE rk <= 10) / (SELECT t FROM tot), 1) AS top10_pct,
  round(100.0 * sum(usd) FILTER (WHERE rk <= 25) / (SELECT t FROM tot), 1) AS top25_pct,
  round(100.0 * sum(usd) FILTER (WHERE rk <= 50) / (SELECT t FROM tot), 1) AS top50_pct
FROM ranked;
--  distinct_funders 683 · research_usd $8,494,017,206
--  top1 7.8% · top10 56.0% · top25 74.8% · top50 85.0%

-- ============================================================================
-- (4) The largest funders of disclosed research, top 12 by dollars. Vaccine and
--     metabolic-drug makers lead — a different ladder from the general-payment
--     ranking, where device royalties dominate.
-- ============================================================================
SELECT
  manufacturer_name,
  count(*)                                                          AS records,
  round(sum(total_amount_usd))                                      AS total_usd,
  round(100.0 * sum(total_amount_usd)
        / sum(sum(total_amount_usd)) OVER (), 1)                   AS pct_of_research
FROM public.cms_open_payments
WHERE program_year = 2024 AND record_type = 'research'
GROUP BY manufacturer_name
ORDER BY total_usd DESC
LIMIT 12;
--  ModernaTX, Inc.                        $662,825,760   7.8%
--  Novartis Pharmaceuticals Corporation   $654,033,055   7.7%
--  Eli Lilly and Company                  $642,014,681   7.6%
--  PFIZER INC.                            $626,184,002   7.4%
--  Merck Sharp & Dohme LLC                $498,210,528   5.9%
--  ABBVIE INC.                            $428,434,576   5.0%
--  AstraZeneca Pharmaceuticals LP         $345,584,468   4.1%
--  Genentech, Inc.                        $341,045,829   4.0%
--  Janssen Research & Development, LLC    $340,333,107   4.0%
--  E.R. Squibb & Sons, L.L.C.             $217,975,186   2.6%
--  GlaxoSmithKline, LLC.                  $210,445,131   2.5%
--  Amgen Inc.                             $196,729,122   2.3%

-- ============================================================================
-- (5) CONTRAST — funder concentration, research vs general. The same query over
--     general payments shows a far broader field: 1,763 funders, top 10 = 32.4%,
--     top 25 = 52.3%. Research dollars concentrate among roughly a third as many
--     funders, and far more tightly.
-- ============================================================================
WITH m AS (
  SELECT record_type, manufacturer_name, sum(total_amount_usd) AS usd
  FROM public.cms_open_payments
  WHERE program_year = 2024 AND record_type IN ('research','general')
  GROUP BY record_type, manufacturer_name
),
ranked AS (
  SELECT record_type, usd,
    row_number() OVER (PARTITION BY record_type ORDER BY usd DESC) AS rk,
    sum(usd) OVER (PARTITION BY record_type)                       AS grp_total
  FROM m
)
SELECT
  record_type,
  count(*)                                                          AS distinct_funders,
  round(100.0 * sum(usd) FILTER (WHERE rk <= 10) / max(grp_total), 1) AS top10_pct,
  round(100.0 * sum(usd) FILTER (WHERE rk <= 25) / max(grp_total), 1) AS top25_pct
FROM ranked
GROUP BY record_type
ORDER BY record_type;
--  general   1,763 funders · top10 32.4% · top25 52.3%
--  research    683 funders · top10 56.0% · top25 74.8%
