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"),
reviewsWithScheduledIntervals AS (SELECT *, TIMESTAMP_DIFF(LAG(nextDueTimestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), DAY) AS scheduledIntervalDays FROM `logs.reviews`),
sessionReviews AS (SELECT userID, cardID, reviewMarking, beforeInterval, scheduledIntervalDays, isRetry, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS sessionNumber FROM `reviewsWithScheduledIntervals` 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),
validUsers AS (SELECT userID FROM `logs.reviews` WHERE beforeInterval > 1000*60*60*24*5 AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND sessionID IS NOT NULL GROUP BY userID HAVING COUNT(DISTINCT cardID) >= 103),
samples AS (SELECT s.userID, s.cardID, s.reviewMarking, s.scheduledIntervalDays FROM sessionReviews AS s JOIN firstRemembered AS f ON (s.userID = f.userID AND s.cardID = f.cardID AND s.sessionNumber = f.sessionNumber + 1) JOIN validUsers AS v ON (s.userID = v.userID) WHERE isRetry IS NOT TRUE)
SELECT scheduledIntervalDays, COUNT(*) AS N, COUNT(DISTINCT userID) AS userCount, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy FROM samples GROUP BY scheduledIntervalDays