29. Institutions collaborations

Level: Medium

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

Description

This query generates pairs of institutions and counts how many publications are shared between authors from those institutions. For example, if authors from the University of Cambridge published 15 papers with authors from the University of Maribor, the Cambridge/Maribor collaborations count would be 15. This does not account for number of authors from these institutions—each publication is counted once per pair of institutions.

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-institution pair will have two entries in the results: One for (Institution 1, Institution 2) and another for (Institution 2, Institution 1). If you want to prevent duplicates, adding something like WHERE org1_id > org2_id will arbitrarily pick one of the combinations to display.

Query

SELECT g1.name AS org1 ,g2.name AS org2
  ,COUNT(p.id) AS collaborations
FROM `dimensions-ai.data_analytics.publications` p
CROSS JOIN UNNEST(research_orgs) org1_id
CROSS JOIN UNNEST(research_orgs) org2_id
INNER JOIN dimensions-ai.data_analytics.grid g1
  ON org1_id=g1.id
INNER JOIN dimensions-ai.data_analytics.grid g2
  ON org2_id=g2.id
WHERE org1_id <> org2_id
  ---- Here is where you can add filters for which
  --   publications to evaluate
  AND p.year >= 2019
  AND '2101' IN UNNEST(p.category_for.second_level.codes) -- Archaeology
GROUP BY 1,2
ORDER BY 3 DESC

Results

Row	org1	org2	collaborations
0	Rovira i Virgili University	Catalan Institute of Human Paleoecology and Social Evolution	444
1	Catalan Institute of Human Paleoecology and Social Evolution	Rovira i Virgili University	444
2	Institute of Vertebrate Paleontology and Paleoanthropology	University of Chinese Academy of Sciences	134
3	University of Chinese Academy of Sciences	Institute of Vertebrate Paleontology and Paleoanthropology	134
4	Rovira i Virgili University	National Research Center on Human Evolution	113