WITH
users AS (SELECT userID, schedule FROM `logs.registeredUsers` WHERE schedule="original"),
forgottenInEssay AS (SELECT userID, cardID FROM (SELECT * EXCEPT (reviewCount), RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewCount 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 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 * FROM logs.reviews WHERE userID="K4JiS8MPW3TUs4dWCYCnBpIhc213" AND cardID="xS1sny5dpoCj5zYIUdxM" ORDER BY timestamp
-- SELECT * FROM forgottenInEssay
SELECT beforeInterval, COUNT(*) AS N, COUNT(DISTINCT userID) AS userCount, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy FROM samples GROUP BY beforeInterval