Where NIH research money goes: a concentration story, 2026

-- Where NIH research money goes — and how few institutions and states capture
-- most of it. Fully reproducible query.
--
-- Question: of the federal research dollars NIH awarded in its most recent
-- complete fiscal year, how concentrated is the funding by awardee institution
-- and by state? The lead figure: of the 2,682 institutions that won NIH
-- research dollars in fiscal year 2025, the top 100 (3.7% of awardees) captured
-- 73.3% of the $41.4 billion awarded. Concentration is a structure-of-funding
-- signal, NOT a quality, merit, or wrongdoing signal of any kind, and nothing
-- here describes or ranks any individual principal investigator.
--
-- Source:
--   public.nih_reporter_projects — NIH RePORTER public project file, pulled
--     from the NIH RePORTER API v2 (api.reporter.nih.gov/v2/projects/search).
--     179,957 funded-award records across FY2024-2026; 81,521 distinct core
--     projects; snapshot updated_at 2026-06-14. Public, key-less, public domain
--     (U.S.-government works). methodology_version = 'nih-reporter/v1'.
--
-- Grain: one row per appl_id — a single funded application/award action. In
--   FY2025 there are 72,550 rows and 72,550 distinct appl_id (no duplication),
--   so summing award_amount across rows is the total of award dollars. A
--   "core project" (core_project_num) groups the multi-year/sub-project actions
--   under one grant; FY2025 holds 57,988 distinct core projects.
--
-- Reference period: FY2025 is the most recent COMPLETE fiscal year on the file
--   (FY2026 is partial — only 27,692 rows at this snapshot), so every
--   concentration figure below is computed over FY2025 to avoid a partial-year
--   artifact. award_amount is NULL on 1,822 of the 72,550 FY2025 rows (training
--   awards and other actions with no dollar figure published); those rows are
--   excluded from dollar sums and from the institution/state denominators.

-- ============================================================================
-- (1) Universe reconciliation — the published file at a glance, by fiscal year.
-- ============================================================================
SELECT
  fiscal_year,
  count(*)                                            AS award_rows,
  count(DISTINCT core_project_num)                    AS distinct_projects,
  count(DISTINCT organization_name)                   AS awardee_orgs,
  count(*) FILTER (WHERE award_amount IS NULL)        AS null_amount,
  round(sum(award_amount) / 1e9, 2)                   AS funded_b
FROM public.nih_reporter_projects
GROUP BY fiscal_year
ORDER BY fiscal_year;
--  2024  79,715 rows · 2,900 orgs · $42.26B
--  2025  72,550 rows · 2,753 orgs · $41.40B   <- most recent COMPLETE year
--  2026  27,692 rows ·   896 orgs · $15.15B   (partial)
--  whole file: 179,957 rows · 81,521 distinct core projects · $98.81B ·
--  56 distinct organization_state codes · 38 funding ICs · snapshot 2026-06-14.

-- ============================================================================
-- (2) HEADLINE: institutional concentration in FY2025. Rank every awardee
--     institution by total award dollars, then read the cumulative share of
--     the top N. The top 100 of 2,682 institutions hold 73.3% of the money.
-- ============================================================================
WITH y AS (
  SELECT * FROM public.nih_reporter_projects
  WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
),
org AS (
  SELECT organization_name, sum(award_amount) AS amt
  FROM y GROUP BY organization_name
),
r AS (
  SELECT *, row_number() OVER (ORDER BY amt DESC) AS rk,
         sum(amt) OVER () AS total, count(*) OVER () AS n_orgs
  FROM org
)
SELECT
  max(n_orgs)                                                       AS awardee_orgs,
  round(max(total) / 1e9, 2)                                        AS total_b,
  round(100.0 * sum(amt) FILTER (WHERE rk <= 10)  / max(total), 1)  AS top10_pct,
  round(100.0 * sum(amt) FILTER (WHERE rk <= 25)  / max(total), 1)  AS top25_pct,
  round(100.0 * sum(amt) FILTER (WHERE rk <= 50)  / max(total), 1)  AS top50_pct,
  round(100.0 * sum(amt) FILTER (WHERE rk <= 100) / max(total), 1)  AS top100_pct
FROM r;
--  awardee_orgs 2,682 · total $41.40B
--  top 10 = 19.4% · top 25 = 38.3% · top 50 = 56.2% · top 100 = 73.3%
--  (top 100 = 3.7% of the 2,682 awardee institutions.)

-- ============================================================================
-- (3) The ten largest awardee institutions, FY2025, by total award dollars and
--     share of the national total. These ten alone hold 19.4% of the file.
-- ============================================================================
SELECT
  organization_name,
  organization_state,
  count(*)                                                          AS awards,
  round(sum(award_amount) / 1e6, 1)                                 AS amount_m,
  round(100.0 * sum(award_amount)
        / (SELECT sum(award_amount) FROM public.nih_reporter_projects
           WHERE fiscal_year = 2025 AND award_amount IS NOT NULL), 2) AS pct_of_all
FROM public.nih_reporter_projects
WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
GROUP BY organization_name, organization_state
ORDER BY sum(award_amount) DESC
LIMIT 10;
--  Johns Hopkins University            MD  1,700  $974.0M  2.35%
--  University of California, San Fran.  CA  1,699  $919.5M  2.22%
--  Washington University               MO  1,405  $841.6M  2.03%
--  University of Pennsylvania          PA  1,460  $824.6M  1.99%
--  University of Michigan at Ann Arbor MI  1,507  $819.9M  1.98%
--  University of Pittsburgh            PA  1,325  $762.6M  1.84%
--  Yale University                     CT  1,276  $736.2M  1.78%
--  Stanford University                 CA  1,324  $729.4M  1.76%
--  Duke University                     NC  1,104  $724.8M  1.75%
--  Massachusetts General Hospital      MA  1,187  $700.3M  1.69%

-- ============================================================================
-- (4) THE LONG TAIL — most institutions get very little. 83.1% of awardee
--     institutions drew under $5M each and split just 5.2% of the money, and
--     1,598 of the 2,682 (59.6%) received exactly one award all year.
-- ============================================================================
WITH y AS (
  SELECT * FROM public.nih_reporter_projects
  WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
),
org AS (
  SELECT organization_name, count(*) AS awards, sum(award_amount) AS amt
  FROM y GROUP BY organization_name
)
SELECT
  count(*)                                                          AS awardee_orgs,
  count(*) FILTER (WHERE amt < 5e6)                                 AS orgs_under_5m,
  round(100.0 * count(*) FILTER (WHERE amt < 5e6) / count(*), 1)    AS pct_orgs_under_5m,
  round(100.0 * sum(amt) FILTER (WHERE amt < 5e6) / sum(amt), 1)    AS dollar_share_under_5m,
  count(*) FILTER (WHERE awards = 1)                                AS single_award_orgs,
  round(100.0 * count(*) FILTER (WHERE awards = 1) / count(*), 1)   AS pct_single_award
FROM org;
--  2,682 awardee orgs · 2,228 under $5M (83.1%) holding 5.2% of dollars ·
--  1,598 single-award orgs (59.6%).

-- ============================================================================
-- (5) WHERE the money lands — top 10 states by FY2025 award dollars, with each
--     state's share of the national total. Institutions in 10 states capture
--     64.0% of NIH dollars; the top three (CA, NY, MA) alone take 32.8%.
-- ============================================================================
WITH y AS (
  SELECT * FROM public.nih_reporter_projects
  WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
    AND organization_state IS NOT NULL
),
st AS (
  SELECT organization_state, count(*) AS awards, sum(award_amount) AS amt
  FROM y GROUP BY organization_state
),
r AS (
  SELECT *, row_number() OVER (ORDER BY amt DESC) AS rk,
         sum(amt) OVER () AS total, count(*) OVER () AS n_states
  FROM st
)
SELECT organization_state, awards,
       round(amt / 1e9, 2)               AS amount_b,
       round(100.0 * amt / total, 1)     AS pct_of_all,
       rk
FROM r WHERE rk <= 10 ORDER BY rk;
--  CA $5.82B 14.1% · NY $3.95B 9.5% · MA $3.80B 9.2% · PA $2.58B 6.2% ·
--  MD $2.36B 5.7% · TX $2.13B 5.2% · NC $1.91B 4.6% · WA $1.42B 3.4% ·
--  IL $1.39B 3.4% · MI $1.13B 2.7%
--  top 3 = 32.8% · top 5 = 44.7% · top 10 = 64.0% · the other 46 codes
--  (of 56) split the remaining 36.0%.

-- ============================================================================
-- (6) WHICH NIH institutes write the checks — top 8 funding ICs in FY2025 by
--     award dollars. The National Cancer Institute alone funds 15.1% of the
--     dollars; the top three ICs fund 39.2% (exact aggregate; the displayed
--     per-IC shares of 15.1 + 13.1 + 11.1 sum to 39.3 only because each is
--     rounded — the unrounded top-3 share is 39.18%, which rounds to 39.2%).
-- ============================================================================
SELECT
  agency_ic_admin,
  count(*)                                                          AS awards,
  round(sum(award_amount) / 1e9, 2)                                 AS amount_b,
  round(100.0 * sum(award_amount)
        / (SELECT sum(award_amount) FROM public.nih_reporter_projects
           WHERE fiscal_year = 2025 AND award_amount IS NOT NULL), 1) AS pct_of_all
FROM public.nih_reporter_projects
WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
GROUP BY agency_ic_admin
ORDER BY sum(award_amount) DESC
LIMIT 8;
--  National Cancer Institute                              $6.24B  15.1%
--  National Institute of Allergy and Infectious Diseases  $5.40B  13.1%
--  National Institute on Aging                            $4.58B  11.1%
--  National Heart Lung and Blood Institute                $3.65B   8.8%
--  National Institute of General Medical Sciences         $3.54B   8.5%
--  National Institute of Neurological Disorders & Stroke  $2.55B   6.2%
--  National Institute of Diabetes & Digestive & Kidney    $2.18B   5.3%
--  National Institute of Mental Health                    $1.90B   4.6%

-- (6b) The exact top-3-IC aggregate behind the 39.2% headline — computed as a
--      single share so it does not depend on summing the rounded per-IC shares.
WITH ic AS (
  SELECT agency_ic_admin, sum(award_amount) AS amt
  FROM public.nih_reporter_projects
  WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
  GROUP BY agency_ic_admin
),
r AS (
  SELECT *, row_number() OVER (ORDER BY amt DESC) AS rk, sum(amt) OVER () AS total
  FROM ic
)
SELECT round(100.0 * sum(amt) FILTER (WHERE rk <= 3) / max(total), 1) AS top3_ic_pct
FROM r;
--  top3_ic_pct 39.2   (exact 39.1848%)

-- ============================================================================
-- (7) THE TOP-VS-TAIL CONTRAST, exact. The single largest awardee institution
--     drew more in FY2025 than the 1,300 smallest-funded institutions did
--     combined: $974.0M vs $541.6M.
-- ============================================================================
WITH y AS (
  SELECT * FROM public.nih_reporter_projects
  WHERE fiscal_year = 2025 AND award_amount IS NOT NULL
),
org AS (
  SELECT organization_name, sum(award_amount) AS amt
  FROM y GROUP BY organization_name
),
r AS (
  SELECT *, row_number() OVER (ORDER BY amt DESC) AS rk, count(*) OVER () AS n
  FROM org
)
SELECT
  round((SELECT amt FROM r WHERE rk = 1) / 1e6, 1)               AS top1_m,
  round((SELECT sum(amt) FROM r WHERE rk > n - 1300) / 1e6, 1)   AS bottom_1300_m
FROM r LIMIT 1;
--  top1_m 974.0  ·  bottom_1300_m 541.6
--  (the largest single institution outdraws the 1,300 smallest combined.)