16. Citations by journal, for a specific publisher

Level: Advanced

This query requires a good understanding of SQL and the Dimensions data model

Description

This query returns a list of journals that have cited a publisher's articles in 2020, ordered by how many citations appeared in each journal.

Query

WITH publisher_pubs AS (
  -- get a list of all publication IDs associated with a single publisher
  SELECT id FROM `dimensions-ai.data_analytics.publications`
  WHERE
    publisher.id = "pblshr.1000340"  -- Public Library of Science (PLoS)
    AND type = "article"
)

-- then find all publications that CITE that publisher's papers
SELECT
  COUNT(p.id) as tot,
  p.journal.title as journal
FROM `dimensions-ai.data_analytics.publications` p,
  UNNEST(p.reference_ids) r
WHERE
  p.year = 2020 AND p.type = "article"      -- restrict to articles with a published year of 2020
  AND p.publisher.id <> "pblshr.1000340"    -- where the publisher is not the same as the pusblisher above
  AND r IN (SELECT id FROM publisher_pubs)   -- the publication must reference a publishers publication
GROUP BY journal
ORDER BY tot DESC
LIMIT 10

Results

[
  {
    "tot": "26309",
    "journal": "Scientific Reports"
  },
  {
    "tot": "18911",
    "journal": "International Journal of Molecular Sciences"
  },
  {
    "tot": "8533",
    "journal": "Frontiers in Microbiology"
  },
  {
    "tot": "7787",
    "journal": "Frontiers in Immunology"
  },
  {
    "tot": "6999",
    "journal": "International Journal of Environmental Research and Public Health"
  },
  {
    "tot": "6446",
    "journal": "Nature Communications"
  },
  {
    "tot": "6199",
    "journal": "Cells"
  },
  {
    "tot": "5706",
    "journal": "Cancers"
  },
  {
    "tot": "5036",
    "journal": "Microorganisms"
  },
  {
    "tot": "5019",
    "journal": "Nutrients"
  }
]