Researchers' first name variations, based on ORCID

First name variation based on ORCID, as present in Dimensions' open data platform.Query usedWITH name_variant AS ( SELECT TRIM(LOWER(pname.given_names)) AS first_name, TRIM(LOWER(variant.content)) AS variant_name, COUNT(DISTINCT s.orcid_identifier.path) AS researcher_count FROM ds-open-datasets.orcid.summaries_2024 AS s, UNNEST(s.person.other_names.names) AS variant JOIN UNNEST([s.person.name]) AS pname WHERE pname.given_names IS NOT NULL AND variant.content IS NOT NULL AND TRIM(LOWER(pname.given_names)) != TRIM(LOWER(variant.content)) AND NOT REGEXP_CONTAINS(variant.content, r"\s") -- remove variants with whitespace AND NOT REGEXP_CONTAINS(variant.content, r"[\?\.,]") -- remove variants with punctuation AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"^(dr|professor|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"\b(dr|professor|mr|ms|mrs|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") GROUP BY first_name, variant_name ORDER BY researcher_count DESC)SELECT *FROM name_varianWITH name_variant AS ( SELECT TRIM(LOWER(pname.given_names)) AS first_name, TRIM(LOWER(variant.content)) AS variant_name, COUNT(DISTINCT s.orcid_identifier.path) AS researcher_count FROM ds-open-datasets.orcid.summaries_2024 AS s, UNNEST(s.person.other_names.names) AS variant JOIN UNNEST([s.person.name]) AS pname WHERE pname.given_names IS NOT NULL AND variant.content IS NOT NULL AND TRIM(LOWER(pname.given_names)) != TRIM(LOWER(variant.content)) AND NOT REGEXP_CONTAINS(variant.content, r"\s") -- remove variants with whitespace AND NOT REGEXP_CONTAINS(variant.content, r"[\?\.,]") -- remove variants with punctuation AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"^(dr|professor|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") AND NOT REGEXP_CONTAINS(LOWER(variant.content), r"\b(dr|professor|mr|ms|mrs|phd|doctor|n/a|reviewer|sociologist|lecturer|lecture|researcher|architect|everyone|physician)\b") GROUP BY first_name, variant_name ORDER BY researcher_count DESC)SELECT *FROM name_variantWHERE researcher_count > 1;WHERE researcher_count > 1;