WITH
users AS (SELECT userID, MAX(sessionNumber) AS lastSessionNumber, TIMESTAMP_DIFF(MAX(studyTimestamp), ANY_VALUE(registrationTimestamp), DAY) AS days FROM `logs.compliance` WHERE sessionNumber >= 30 AND TIMESTAMP_DIFF(studyTimestamp, registrationTimestamp, DAY) >= 9*30 GROUP BY userID)
SELECT users.*, email from users JOIN `logs.registeredUsers` USING (userID)