WITH
users AS (SELECT * FROM `logs.registeredUsers` WHERE schedule NOT IN UNNEST([“original”, “aggressiveStart”])),
withRank AS (SELECT *, COUNTIF(reviewMarking=“remembered” AND isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS priorCorrect, COUNTIF(sessionID IS NOT NULL AND isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp) as repetitionNumber FROM `logs.reviews` WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName=“qcvc”))
SELECT schedule AS schedule, COUNT(DISTINCT userID) AS N, COUNTIF(reviewMarking=“remembered”) / COUNT(*) AS accuracy FROM withRank JOIN users USING (userID) WHERE repetitionNumber = 1 AND priorCorrect = 1 GROUP BY schedule ORDER BY schedule