GBQ Dimensions: Getting researchers based on two criteria #sql

researchers.sql

-- Pub criteria:
--   FOR code
--   Specific journals 
--   Year

-- Author criteria:
--   FCR (of any paper for last 5 years) > 1.5
--   Last publication < 24 months ago
--   Total publications > 7




WITH
  researchers_fcr AS (
    SELECT res_id
  FROM
    `dimensions-ai.data_analytics.publications` p, 
    UNNEST(researcher_ids) res_id
  WHERE
    metrics.field_citation_ratio > 1.5
    AND year >= 2015
  )

, researchers_pubs AS (
  SELECT res_id
  FROM
    `dimensions-ai.data_analytics.publications` p,
    UNNEST(category_for.first_level.full) cat, 
    UNNEST(researcher_ids) res_id
  WHERE
    cat.name = "Chemical Sciences" 
    AND (journal.id = "jour.1047592" OR journal.id = "jour.1039133" OR journal.id = "jour.1157000" OR journal.id = "jour.1046724" OR journal.id = "jour.1049812" OR journal.id = "jour.1319511")
    AND year >= 2015
    )

, final_researchers AS (
  SELECT * FROM researchers_fcr 
  INTERSECT DISTINCT
  SELECT * FROM researchers_pubs 
)

SELECT
  COUNT(DISTINCT res_id) AS total
FROM
  final_researchers 
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 >= 8
  AND r.last_publication_year >= 2019
  AND g.address.country IN ("Japan", "United Kingdom", "South Korea", "Germany", "Italy", "France", "Spain", "Australia")