10. Select publications matching selected concepts

Level: Medium

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

Description

This query counts the number of publications about a set of tropical diseases. It filters publications by selecting only those that either refer to those diseases in the title, or that have been tagged with those diseases as concepts within the Dimensions database.

Once publications have been found, the counts are broken down both by year and by publisher; the final list shows the top 10 publisher-years in which the most papers were published about those diseases.

Query

SELECT
  publisher.NAME AS publisher,
  year,
  COUNT(*) AS num_pub
FROM
  `dimensions-ai.data_analytics.publications`,
  UNNEST(concepts) c
WHERE
  (LOWER(c.concept) IN UNNEST(["buruli ulcer", "mycobacterium", "mycolactone", "bairnsdale ulcer"])
    OR REGEXP_CONTAINS(title.preferred, r"(?i)/buruli ulcer|mycobacterium|mycolactone|bairnsdale ulcer/"))
  AND year >= 2010
  AND publisher IS NOT NULL
GROUP BY
  publisher,
  year
ORDER BY
  num_pub DESC,
  year,
  publisher
LIMIT 10

Results

[
  {
    "publisher": "Elsevier",
    "year": "2020",
    "num_pub": "31602"
  },
  {
    "publisher": "Elsevier",
    "year": "2018",
    "num_pub": "29639"
  },
  {
    "publisher": "Elsevier",
    "year": "2019",
    "num_pub": "28941"
  },
  {
    "publisher": "Elsevier",
    "year": "2017",
    "num_pub": "28415"
  },
  {
    "publisher": "Elsevier",
    "year": "2015",
    "num_pub": "27299"
  },
  {
    "publisher": "Elsevier",
    "year": "2011",
    "num_pub": "25757"
  },
  {
    "publisher": "Elsevier",
    "year": "2016",
    "num_pub": "25149"
  },
  {
    "publisher": "Elsevier",
    "year": "2013",
    "num_pub": "23205"
  },
  {
    "publisher": "Elsevier",
    "year": "2014",
    "num_pub": "22952"
  },
  {
    "publisher": "Springer Nature",
    "year": "2019",
    "num_pub": "22072"
  }
]