GBQ Dimensions: return researchers and their publication count #sql

researchers1.sql

-- Return researchers and their publication count
--    LIMITED TO
--    publications with FOR “11 Medical and Health Sciences”
--    WHERE
--    Researcher is from Canada, Austria, Belgium, Czech Rep, Denmark, Finland, France, Australia
--    Researcher did NOT publish in the 2018, 2019, 2020 in a journal from publisher “Public Library of Science (PLoS)”
--    Researcher has email address
--    number of publications is minimum 3
--    sorted by
--    publication count



WITH
  pubs AS (
  SELECT
    p.id,
    p.researcher_ids
  FROM
    `dimensions-ai.data_analytics.publications` p,
    UNNEST(category_for.first_level.full) cat
  WHERE
    cat.name = "Medical and Health Sciences"
    AND NOT ( year >= 2018
      AND year <= 2020
      AND publisher.name = "Public Library of Science (PLoS)") )
SELECT
  COUNT(DISTINCT p.id) tot_pubs,
  res_id,
  r.first_name,
  r.last_name,
  r.current_research_org,
  email[ORDINAL(1)] as email_first,
  g.address.country
FROM
  pubs p,
  UNNEST(researcher_ids) res_id
JOIN
  `dimensions-ai.data_analytics.researchers` r
ON
  r.id = res_id
JOIN
  `dimensions-ai.data_analytics.grid` g
ON
  g.id = r.current_research_org
WHERE
  ARRAY_LENGTH(r.email) > 0
  AND r.total_publications >= 3
  AND g.address.country IN ("Canada",
    "Austria",
    "Belgium",
    "Czechia",
    "Denmark",
    "Finland",
    "France",
    "Australia")
GROUP BY
  res_id,
  r.first_name,
  r.last_name,
  r.current_research_org,
  g.address.country,
  email_first
ORDER BY
  -- tot_pubs DESC
  RAND() -- for a random ordering