19. Outgoing citations from a journal

Level: Medium

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

Description

This query counts outgoing citations per year from a single journal. Results are broken down by year and by the type of publication being cited (article, book, etc).

Note

There is an important clause in the SELECT statement that changes the behavior of this query: If you use COUNT(DISTINCT id), the query counts unique publications that have been cited in the selected journal. If you use COUNT(id) instead, this counts citations: If one publication it cited by multiple papers in a single journal, the latter query will count each citation separately.

Query

SELECT
  COUNT(DISTINCT id) AS totcount,  year, type
FROM
  `dimensions-ai.data_analytics.publications`
WHERE
  id IN (
    SELECT DISTINCT reference_pubs
    FROM
      `dimensions-ai.data_analytics.publications`,
      UNNEST(reference_ids) AS reference_pubs
    WHERE journal.id = "jour.1115214" -- Nature Biotechnology
  )
  AND year >= 2005
GROUP BY year, type
ORDER BY year, type

Results

[
  {
    "totcount": "3757",
    "year": "2005",
    "type": "article"
  },
  {
    "totcount": "12",
    "year": "2005",
    "type": "book"
  },
  {
    "totcount": "60",
    "year": "2005",
    "type": "chapter"
  },
  {
    "totcount": "9",
    "year": "2005",
    "type": "monograph"
  },
  {
    "totcount": "8",
    "year": "2005",
    "type": "proceeding"
  },
  // more entries here...
]