WITH
users AS (SELECT userID, schedule FROM `logs.registeredUsers`),
forgottenInEssay AS (SELECT userID, cardID FROM `logs.reviews` 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, TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), HOUR) AS delay, TIMESTAMP_DIFF(LAG(nextDueTimestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), HOUR) AS scheduledDelay, COUNTIF(sessionID IS NOT NULL) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS sessionNumber, COUNTIF(isRetry IS TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS previousRetries FROM `logs.reviews` JOIN forgottenInEssay USING (userID, cardID)),
reviewSamples AS (SELECT userID, cardID, reviewMarking, beforeInterval, delay FROM sessionReviews WHERE sessionNumber = 1 AND isRetry IS NOT TRUE AND previousRetries > 0 AND scheduledDelay = beforeInterval/(1000*60*60))
SELECT beforeInterval, COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy, COUNT(*) AS N, COUNT(DISTINCT userID) AS userCount, ROUND(delay/24) as dayBucket FROM reviewSamples JOIN users USING (userID) WHERE (schedule="aggressiveStart" AND beforeInterval=432000000) OR (schedule!="aggressiveStart" AND schedule!="original" AND beforeInterval=86400000) GROUP BY beforeInterval, dayBucket HAVING COUNT(DISTINCT userID) > 50 ORDER BY beforeInterval, dayBucket