WITH
users AS (SELECT userID FROM `logs.registeredUsers` WHERE schedule="aggressiveStart"),
sessionReviews AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber FROM `logs.reviews` JOIN users USING (userID) WHERE sessionID IS NOT NULL AND isRetry IS NOT TRUE),
inEssayAccuracies AS (SELECT userID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS essayAccuracy FROM `logs.reviews` JOIN users USING (userID) WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND reviewCount = 1 AND sessionID IS NULL AND isRetry IS NOT TRUE GROUP BY userID),
samples AS (SELECT userID, reviewMarking, cardID, reviewNumber, essayAccuracy FROM sessionReviews JOIN inEssayAccuracies USING (userID) WHERE (cardID="s8duZcGBbu0dxb4xEAGg" OR cardID="xiNW1zgeb2ITHGi6uQtg"))
SELECT cardID, reviewNumber, COUNTIF(reviewMarking="remembered")/COUNT(*) AS recallRate, COUNT(*) AS N FROM samples WHERE essayAccuracy <= 0.83 GROUP BY reviewNumber, cardID ORDER BY cardID, reviewNumber