20220120170540

WITH
  users AS (
  SELECT
    * EXCEPT (schedule), SUBSTR(schedule, 0, 9) AS schedule
  FROM
    `logs.registeredUsers` WHERE timestamp < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 120 DAY) AND timestamp >= TIMESTAMP("2021-04-19")),
validUsers AS (SELECT userID, ANY_VALUE(schedule) AS schedule FROM `logs.reviews` JOIN users USING (userID) WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND reviewCount = 1 AND schedule="2021-04-D" GROUP BY userID HAVING COUNT(*) >= 103)
SELECT * FROM `logs.compliance` JOIN validUsers USING (userID) ORDER BY userID, sessionNumber