22. Incoming citations for a single publication, by journal

Level: Medium

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

Description

This query counts incoming citations for a single publication.

Results are grouped by journal, and include the publisher of each journal in the list.

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 for information about all the IDs in that list.

The COALESCE function is used here to minimize the number of null fields in the final results—if an incoming citation is published in a book rather than a journal, for example, then the journal.title field will be NULL, and the boook_title.preferred field is likely to have the value we want. Occasionally, the publisher field is unavailable, so we use COALESCE(p.publisher.name, "(unknown)") to make sure there aren't any blank fields.

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 here
)

SELECT
    COALESCE(
        p.journal.title,
        CONCAT(p.book_title.preferred, ' (book)'),
        p.proceedings_title.preferred,
        CONCAT(p.title.preferred, ' (book)') -- some books have this field instead of book_title
    ) AS journal,
    COALESCE(p.publisher.name, "(unknown)") AS publisher,
    p.type AS pubtype, COUNT(p.id) AS citations
FROM `dimensions-ai.data_analytics.publications` p
WHERE p.id IN (SELECT id FROM citing)
GROUP BY 1,2,3
ORDER BY 4 DESC

Results

[
  {
    "journal": "bioRxiv",
    "publisher": "Cold Spring Harbor Laboratory",
    "pubtype": "preprint",
    "citations": "15"
  },
  {
    "journal": "PLOS Biology",
    "publisher": "Public Library of Science (PLoS)",
    "pubtype": "article",
    "citations": "5"
  },
  {
    "journal": "eLife",
    "publisher": "eLife",
    "pubtype": "article",
    "citations": "3"
  },
  {
    "journal": "medRxiv",
    "publisher": "Cold Spring Harbor Laboratory",
    "pubtype": "preprint",
    "citations": "3"
  },
  {
    "journal": "Scientometrics",
    "publisher": "Springer Nature",
    "pubtype": "article",
    "citations": "3"
  },
  // more entries here...
]