18. Incoming citations for a journal

Level: Medium

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

Description

This query lists incoming citations per year for a single journal. Results are split out by year and by the type of publication that cited the journal (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 cited the selected journal. If you use COUNT(id) instead, this counts citations: If one publication cites multiple papers from 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 citing_pubs.id
    FROM
      `dimensions-ai.data_analytics.publications`,
      UNNEST(citations) AS citing_pubs
    WHERE journal.id = "jour.1115214" -- Nature Biotechnology
  )
  AND year >= 2005
GROUP BY year, type
ORDER BY year, type

Results

[
  {
    "totcount": "13064",
    "year": "2005",
    "type": "article"
  },
  {
    "totcount": "12",
    "year": "2005",
    "type": "book"
  },
  {
    "totcount": "1492",
    "year": "2005",
    "type": "chapter"
  },
  {
    "totcount": "23",
    "year": "2005",
    "type": "monograph"
  },
  {
    "totcount": "192",
    "year": "2005",
    "type": "proceeding"
  },
  // more entries here...
]