15. Top N publications by citations percentile

Level: Advanced

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

Description

This query sorts all engineering publications from 2020 by their total citations and returns those in the top 1%.

Engineering publications are determined by evaluating Field of Research classifications that are applied to the publications. "09" is the top-level code assigned to "Engineering."

Query

WITH pubs AS (
  SELECT
    p.id as id,
    p.title.preferred as title,
    p.citations_count as citations,
  FROM
    `dimensions-ai.data_analytics.publications` p
  WHERE
    year = 2020
    AND "09" IN UNNEST(category_for.first_level.codes)
),
ranked_pubs AS (
  SELECT
    p.*,
    PERCENT_RANK() OVER (ORDER BY p.citations DESC) citation_percentile
  FROM
    pubs p
)

SELECT * FROM ranked_pubs
WHERE
  citation_percentile <= 0.01
ORDER BY
  citation_percentile ASC

Results

[
  {
    "id": "pub.1129408972",
    "title": "Estimation of total flavonoid content in propolis by two complementary colometric methods",
    "citations": "1014",
    "citation_percentile": "0.0"
  },
  {
    "id": "pub.1122861707",
    "title": "Mercury 4.0: from visualization to analysis, design and prediction",
    "citations": "517",
    "citation_percentile": "1.4502085399880502E-6"
  },
  {
    "id": "pub.1126110231",
    "title": "Covid-19: automatic detection from X-ray images utilizing transfer learning with convolutional neural networks",
    "citations": "373",
    "citation_percentile": "2.9004170799761005E-6"
  },
  {
    "id": "pub.1125814051",
    "title": "Analysis and forecast of COVID-19 spreading in China, Italy and France",
    "citations": "348",
    "citation_percentile": "4.350625619964151E-6"
  },
  {
    "id": "pub.1121839330",
    "title": "A Vision of 6G Wireless Systems: Applications, Trends, Technologies, and Open Research Problems",
    "citations": "327",
    "citation_percentile": "5.800834159952201E-6"
  },
  {
    "id": "pub.1125821215",
    "title": "The Role of Telehealth in Reducing the Mental Health Burden from COVID-19",
    "citations": "307",
    "citation_percentile": "7.251042699940251E-6"
  },
  // many more entries here...
]