As a researcher, you may be interested in the date someone enrolled into the All of Us Research Program as a data contributing participant. The best data point to use is the observation_date when they granted primary consent to the program. NOTE: primary consent is different from Electronic Health Record (EHR) consent which can be granted at a later date. Using primary_consent_date as a proxy for enrollment date, the below SQL will pull this date for all participants. You can then merge this dataframe with others using person_id as the shared variable.
Registered Tier Users:
SELECT DISTINCT
person_id,
observation_date AS primary_consent_date
FROM `fc-aou-cdr-prod.R2022Q2R6.concept`
JOIN `fc-aou-cdr-prod.R2022Q2R6.concept_ancestor` on concept_id = ancestor_concept_id
JOIN `fc-aou-cdr-prod.R2022Q2R6.observation` on descendant_concept_id = observation_concept_id
WHERE concept_name = 'Consent PII' AND concept_class_id = 'Module'
Controlled Tier Users:
SELECT DISTINCT
person_id,
observation_date AS primary_consent_date
FROM `fc-aou-cdr-prod-ct.C2022Q2R6.concept`
JOIN `fc-aou-cdr-prod-ct.C2022Q2R6.concept_ancestor` on concept_id = ancestor_concept_id
JOIN `fc-aou-cdr-prod-ct.C2022Q2R6.observation` on descendant_concept_id = observation_concept_id
WHERE concept_name = 'Consent PII' AND concept_class_id = 'Module'