13. Funding by journal

Level: Medium

This query requires basic knowledge of SQL and the Dimensions data model

Description

This query looks at all publications from a single specified journal and counts the total number of grants associated with those publications.

The results list each funding agency with a count of how many papers it has been linked to, combined with the number of grants from that agency that were referenced by those papers.

Query

WITH funding AS (
  SELECT
    funding.grid_id AS funders,
    COUNT(id) AS pubs,
    COUNT(funding.grant_id) AS grants
  FROM
    `dimensions-ai.data_analytics.publications`,
    UNNEST(funding_details) AS funding
  WHERE
    journal.id = "jour.1113716" -- nature medicine
  GROUP BY
    funders
)

SELECT
  funding.*,
  grid.name
FROM funding
INNER JOIN `dimensions-ai.data_analytics.grid` grid
  ON funding.funders = grid.id
ORDER BY
  pubs DESC,
  grants DESC
LIMIT 10

Results

[
  {
    "funders": "grid.48336.3a",
    "pubs": "2727",
    "grants": "2510",
    "name": "National Cancer Institute"
  },
  {
    "funders": "grid.419681.3",
    "pubs": "2043",
    "grants": "1910",
    "name": "National Institute of Allergy and Infectious Diseases"
  },
  {
    "funders": "grid.419635.c",
    "pubs": "1642",
    "grants": "1584",
    "name": "National Institute of Diabetes and Digestive and Kidney Diseases"
  },
  {
    "funders": "grid.279885.9",
    "pubs": "1641",
    "grants": "1554",
    "name": "National Heart Lung and Blood Institute"
  },
  {
    "funders": "grid.416870.c",
    "pubs": "717",
    "grants": "673",
    "name": "National Institute of Neurological Disorders and Stroke"
  },
  {
    "funders": "grid.419475.a",
    "pubs": "585",
    "grants": "553",
    "name": "National Institute on Aging"
  },
  {
    "funders": "grid.14105.31",
    "pubs": "547",
    "grants": "431",
    "name": "Medical Research Council"
  },
  {
    "funders": "grid.54432.34",
    "pubs": "512",
    "grants": "447",
    "name": "Japan Society for the Promotion of Science"
  },
  {
    "funders": "grid.280785.0",
    "pubs": "465",
    "grants": "446",
    "name": "National Institute of General Medical Sciences"
  },
  {
    "funders": "grid.270680.b",
    "pubs": "410",
    "grants": "187",
    "name": "European Commission"
  }
]