23. Citing authors by country

Level: Medium

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

Description

This query counts incoming citations for a single publication. The final results count authors, rather than publications, and group counts by the country of each author's current affiliation.

The strategy is similar to query 22, which groups citations by journal: the publications table has a citations field that includes the publication IDs of all incoming citations for a given paper. We fetch that list in the citing subquery below, then query the publications table to get a list of authors for all the publications citing the paper of interest. We join the researchers table to get each author's current affiliation. In the final query, we use the grid table to associate each author affiliation to a single country, then group all results by those countries.

Note

The list of citing publications is determined by the clause WHERE p.id='pub.1113640622'. This can be changed to be as broad or narrow as you wish—changing it to something like WHERE journal.title='eLife', for example, would return incoming citations to an entire journal rather than a single paper.

Query

WITH citing AS (
    SELECT citing_pubs.id
    FROM `dimensions-ai.data_analytics.publications` p
    CROSS JOIN UNNEST(citations) AS citing_pubs
    WHERE p.id='pub.1113640622' -- publication of interest
),
people_and_grids as (
  SELECT COUNT(DISTINCT auth.researcher_id) AS people,
    res.current_research_org AS gridid
  FROM `dimensions-ai.data_analytics.publications` pubs
  CROSS JOIN UNNEST(authors) as auth
  INNER JOIN `dimensions-ai.data_analytics.researchers` res
    ON res.id=auth.researcher_id
  WHERE pubs.id IN (SELECT id FROM citing)
  GROUP BY gridid
)

SELECT people, address.country
FROM people_and_grids
INNER JOIN `dimensions-ai.data_analytics.grid` gridinfo
  ON gridinfo.id=people_and_grids.gridid
ORDER BY people DESC

Results

[
  {
    "people": "10",
    "country": "Brazil"
  },
  {
    "people": "5",
    "country": "United States"
  },
  {
    "people": "5",
    "country": "United States"
  },
  {
    "people": "5",
    "country": "United States"
  },
  {
    "people": "4",
    "country": "Croatia"
  },
  // more results here...
]