20. International collaboration of an organisation in a field

Level: Medium

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

Description

This query looks at international collaborations by year, with additional filters for institution and Field of Research. The pubcounts subquery counts the total number of relevant papers that have authors from multiple countries, then the final query divides this number by the total number of relevant papers in that category.

Query

WITH pubcounts AS (
  SELECT year,
    COUNTIF(ARRAY_LENGTH(p.research_org_countries) > 1) AS intl,
    COUNTIF(ARRAY_LENGTH(p.research_org_countries) <= 1) AS domestic
  FROM `dimensions-ai.data_analytics.publications` p
  WHERE
    year >= 2015
    AND "0601" in UNNEST(category_for.second_level.codes) -- field defined here
    AND "grid.4991.5" in UNNEST(research_orgs) -- org defined here
  GROUP BY year
  ORDER BY year DESC
)

SELECT
  pubcounts.year, pubcounts.intl, pubcounts.domestic,
  ROUND(
    (pubcounts.intl*100)/(pubcounts.domestic + pubcounts.intl)
  , 1) AS percentagecollab
FROM pubcounts
ORDER BY year DESC

Results

[
  {
    "year": "2021",
    "intl": "184",
    "domestic": "92",
    "percentagecollab": "66.7"
  },
  {
    "year": "2020",
    "intl": "606",
    "domestic": "307",
    "percentagecollab": "66.4"
  },
  {
    "year": "2019",
    "intl": "534",
    "domestic": "262",
    "percentagecollab": "67.1"
  },
  {
    "year": "2018",
    "intl": "471",
    "domestic": "246",
    "percentagecollab": "65.7"
  },
  {
    "year": "2017",
    "intl": "460",
    "domestic": "277",
    "percentagecollab": "62.4"
  },
  {
    "year": "2016",
    "intl": "422",
    "domestic": "235",
    "percentagecollab": "64.2"
  },
  {
    "year": "2015",
    "intl": "369",
    "domestic": "268",
    "percentagecollab": "57.9"
  }
]