Skip to content

Example Queries and Results

The following are examples of valid Google BigQuery SQL queries that can be executed against the Altmetric Google BigQuery datasets.

Simple Query

1
SELECT COUNT(*) AS total FROM `altmetric.data.research_outputs`

This query will return the total number of publications contained within the research_outputs table.

Row total
1 46,213,445

Retrieving Records

The query below will retrieve the top 5 records from the research_outputs table which have the highest Altmetric Attention scores for the year 2019, returning the DOI (where present), and title of the publication, as well as the Altmetric Attention score.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT
  identifiers.doi,
  title,
  altmetric_score
FROM
  `altmetric.data.research_outputs`
WHERE
  year = 2019
ORDER BY
  altmetric_score desc
LIMIT 5
doi title altmetric_score
10.1038/d41586-019-00857-9 Scientists rise up against statistical significance 12127.04
10.1093/biosci/biz088 World Scientists' Warning of a Climate Emergency 11577.6
null Few-Shot Adversarial Learning of Realistic Neural Talking Head Models 10692.45
10.1073/pnas.1821204116 Risk of being killed by police use of force in the United States by age, race-ethnicity, and sex 10568.76
10.1038/d41586-019-03595-0 Climate tipping points -- too risky to bet against 10381.53

The query below will retrieve the top 5 records from the research_outputs table which have the highest Altmetric Attention scores for the GRID ID grid.26999.3d in the year 2023, returning the DOI and title of the publication, as well as the Altmetric Attention score.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT
  identifiers.doi,
  title,
  altmetric_score
FROM
  `altmetric.data.research_outputs`
INNER JOIN
  `altmetric.data.dim_publications` dp
ON
  (identifiers.dimensions.publication_id=dp.id)
WHERE
  year = 2023
  AND "grid.26999.3d" IN UNNEST(research_orgs)
ORDER BY
  altmetric_score DESC
LIMIT
  5
doi title altmetric_score
10.1038/s41586-023-06692-3 A resonant sextuplet of sub-Neptunes transiting the bright star HD 110067 2707.83
10.1016/s2215-0366(23)00193-1 Age of onset and cumulative risk of mental disorders: a cross-national analysis of population surveys from 29 countries 2262.93
10.1038/s41467-023-36904-3 Uracil in the carbonaceous asteroid (162173) Ryugu 2194.46
10.1038/s41559-023-02041-9 A global phylogeny of butterflies reveals their evolutionary history, ancestral hosts and biogeographic origins 1991.77
10.1126/science.abo5095 An extremely energetic cosmic ray observed by a surface detector array 1938.98

The query below will retrieve the top 5 records from the research_outputs table which have the highest Altmetric Attention scores for the second level ANZSRC 2020 Fields of Research (FOR) 43 History, Heritage, and Archaeology, in the year 2023. It returns the name of the research organization, the organization's GRID ID, the publication's DOI and title, and the Altmetric Attention score.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SELECT
  grid.name,
  org,
  identifiers.doi,
  title,
  altmetric_score
FROM
  `altmetric.data.research_outputs`
INNER JOIN
  `altmetric.data.dim_publications` dp
ON
  (identifiers.dimensions.publication_id=dp.id),
  UNNEST(research_orgs) org
INNER JOIN
  `altmetric.data.dim_grid` grid
ON
  (org=grid.id)
LEFT OUTER JOIN
  UNNEST(category_for.first_level.full) for1
WHERE
  year = 2023
  AND ARRAY_LENGTH(research_orgs)=1
  AND for1.code = "43"
ORDER BY
  altmetric_score DESC
LIMIT
  5
name org doi title altmetric_score
University of Cambridge grid.5335.0 10.1098/rsos.230356 Three-dimensional volumetric muscle reconstruction of the Australopithecus afarensis pelvis and limb, with estimations of limb leverage 1708.3
Dartmouth College grid.254880.3 10.15184/aqy.2023.153 A wall or a road? A remote sensing-based investigation of fortifications on Rome's eastern frontier 1207.45
University of Granada grid.4489.1 10.1126/sciadv.adf6138 Unveiling the secret of ancient Maya masons: Biomimetic lime plasters with plant extracts 1044.44
University of Gottingen grid.7450.6 10.1017/s0959774323000082 Error or Minority? The Identification of Non-binary Gender in Prehistoric Burials in Central Europe 927.13
University of the Balearic Islands grid.9563.9 10.1017/s1047759423000156 Las Georgicas de Virgilio in figlinis: a proposito de un grafito ante cocturam sobre un anfora olearia betica 755.57

More Examples

More examples of using BigQuery to carry out research data analytics can be found at the Dimensions BigQuery Lab.