28. Researcher collaborations

Level: Medium

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

Description

This query generates two-author pairs and counts how many publications are shared between them. The WHERE clause can be used to define a subset of the Dimensions publication dataset to examine—without that, the query will generate co-authorship counts for every author of all 120+ million publications.

Note

Each two-author pair will have two entries in the results: One for (Researcher 1, Researcher 2) and another for (Researcher 2, Researcher 1). If you want to prevent duplicates, adding something like WHERE researcher1_id > researcher2_id will arbitrarily pick one of the combinations to display.

Query

SELECT
  ,CONCAT(r1.first_name, " ", r1.last_name) AS researcher1
  ,g1.name AS researcher1_org
  ,CONCAT(r2.first_name, " ", r2.last_name) AS researcher2
  ,g2.name AS researcher2_org
  ,COUNT(p.id) AS collaborations
FROM `dimensions-ai.data_analytics.publications` p
CROSS JOIN UNNEST(researcher_ids) researcher1_id
CROSS JOIN UNNEST(researcher_ids) researcher2_id
INNER JOIN dimensions-ai.data_analytics.researchers r1
  ON researcher1_id=r1.id
INNER JOIN dimensions-ai.data_analytics.researchers r2
  ON researcher2_id=r2.id
INNER JOIN dimensions-ai.data_analytics.grid g1
  ON r1.current_research_org=g1.id
INNER JOIN dimensions-ai.data_analytics.grid g2
  ON r2.current_research_org=g2.id
WHERE
  AND researcher1_id <> researcher2_id
  ---- Here is where you can add filters for which
  --   publications to evaluate
  p.year >= 2019
  AND '0604' IN UNNEST(category_for.second_level.codes) -- genetics
GROUP BY 1,2,3,4
ORDER BY 5 DESC

Results

Row	researcher1	researcher1_org	researcher2	researcher2_org	collaborations
0	Vasileios A Bampidis	International Hellenic University	Roberto Edoardo Villa	University of Milan	67
1	Roberto Edoardo Villa	University of Milan	Vasileios A Bampidis	International Hellenic University	67
2	Alexander Sergeevich Galushko	Agrophysical Research Institute	Jan Kuever	Leibniz Institute for Materials Engineering	66
3	Jan Kuever	Leibniz Institute for Materials Engineering	Alexander Sergeevich Galushko	Agrophysical Research Institute	66
4	Jerome I Rotter	Harbor–UCLA Medical Center	Kent D Taylor	Harbor–UCLA Medical Center	65