WITH
eligibleUsers AS (SELECT userID FROM `logs.registeredUsers` WHERE schedule="original"),
logs AS (SELECT *, COUNTIF(isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber, COUNTIF(reviewMarking="forgotten") OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lapseCount, TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), HOUR) AS reviewInterval FROM `logs.reviews` JOIN eligibleUsers USING (userID)),
firstReview AS (SELECT userID, cardID FROM logs WHERE reviewNumber = 2 AND lapseCount = 1 AND isRetry IS NOT TRUE AND reviewMarking="remembered")
SELECT COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS N FROM logs JOIN firstReview USING (userID, cardID) WHERE reviewNumber=3 AND isRetry IS NOT TRUE