1SELECT
2  CASE ic_name
3    WHEN 'NATIONAL CANCER INSTITUTE' THEN 'NCI'
4    WHEN 'NATIONAL INSTITUTE OF ALLERGY AND INFECTIOUS DISEASES' THEN 'NIAID'
5    WHEN 'NATIONAL INSTITUTE ON AGING' THEN 'NIA'
6    WHEN 'NATIONAL HEART, LUNG, AND BLOOD INSTITUTE' THEN 'NHLBI'
7    WHEN 'NATIONAL INSTITUTE OF GENERAL MEDICAL SCIENCES' THEN 'NIGMS'
8    WHEN 'NATIONAL INSTITUTE OF NEUROLOGICAL DISORDERS AND STROKE' THEN 'NINDS'
9    WHEN 'NATIONAL INSTITUTE OF DIABETES AND DIGESTIVE AND KIDNEY DISEASES' THEN 'NIDDK'
10    WHEN 'NATIONAL INSTITUTE OF MENTAL HEALTH' THEN 'NIMH'
11    WHEN 'NATIONAL INSTITUTE ON DRUG ABUSE' THEN 'NIDA'
12    WHEN 'EUNICE KENNEDY SHRIVER NATIONAL INSTITUTE OF CHILD HEALTH & HUMAN DEVELOPMENT' THEN 'NICHD'
13    ELSE ic_name
14  END AS institute,
15  ROUND(SUM(try_cast (total_cost AS BIGINT)) / 1e9, 2) AS funding_b,
16  COUNT(*) AS num_projects,
17  ROUND(AVG(try_cast (total_cost AS BIGINT)) / 1e3, 0) AS avg_grant_k,
18  ROUND(MEDIAN(try_cast (total_cost AS BIGINT)) / 1e3, 0) AS median_grant_k,
19  ROUND(MAX(try_cast (total_cost AS BIGINT)) / 1e3, 0) AS max_grant_k
20FROM
21  "@nih.projects.projects"
22WHERE
23  fy = 2025
24  AND total_cost IS NOT NULL
25  AND try_cast (total_cost AS BIGINT) > 0
26  AND ic_name IN (
27    'NATIONAL CANCER INSTITUTE',
28    'NATIONAL INSTITUTE OF ALLERGY AND INFECTIOUS DISEASES',
29    'NATIONAL INSTITUTE ON AGING',
30    'NATIONAL HEART, LUNG, AND BLOOD INSTITUTE',
31    'NATIONAL INSTITUTE OF GENERAL MEDICAL SCIENCES',
32    'NATIONAL INSTITUTE OF NEUROLOGICAL DISORDERS AND STROKE',
33    'NATIONAL INSTITUTE OF DIABETES AND DIGESTIVE AND KIDNEY DISEASES',
34    'NATIONAL INSTITUTE OF MENTAL HEALTH',
35    'NATIONAL INSTITUTE ON DRUG ABUSE',
36    'EUNICE KENNEDY SHRIVER NATIONAL INSTITUTE OF CHILD HEALTH & HUMAN DEVELOPMENT'
37  )
38GROUP BY
39  ic_name
40ORDER BY
41  funding_b DESC