WITH
users AS (SELECT userID, schedule FROM `logs.registeredUsers` WHERE schedule="aggressiveStart"),
forgottenInEssay AS (SELECT userID, cardID FROM `logs.reviews` JOIN users USING (userID) WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND reviewCount = 1 AND reviewMarking = "forgotten"),
sessionReviews AS (SELECT userID, cardID, reviewMarking, beforeInterval, isRetry, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS sessionNumber FROM `logs.reviews` JOIN forgottenInEssay USING (userID, cardID) WHERE sessionID IS NOT NULL),
firstRemembered AS (SELECT MIN(sessionNumber) AS sessionNumber, userID, cardID FROM sessionReviews WHERE beforeInterval=432000000 AND isRetry IS NOT TRUE AND reviewMarking="remembered" GROUP BY userID, cardID),
samples AS (SELECT s.userID, s.cardID, s.reviewMarking, s.beforeInterval FROM sessionReviews AS s JOIN firstRemembered AS f ON (s.userID = f.userID AND s.cardID = f.cardID AND s.sessionNumber = f.sessionNumber + 1) WHERE isRetry IS NOT TRUE)
SELECT beforeInterval, COUNT(*) AS N, COUNT(DISTINCT userID) AS userCount, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy FROM samples GROUP BY beforeInterval