20211011111522

WITH
users AS (SELECT * FROM `logs.registeredUsers` WHERE schedule NOT IN UNNEST([“original”, “aggressiveStart”])),
withRank AS (SELECT *, COUNTIF(isRetry IS NOT TRUE AND reviewMarking = “remembered”) 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”)),
withAccuracies AS (SELECT userID, ANY_VALUE(schedule) AS schedule, SAFE_DIVIDE(COUNTIF(reviewMarking=“remembered” AND priorCorrect = 1), COUNTIF(priorCorrect = 1)) AS accuracy FROM withRank JOIN users USING (userID) WHERE repetitionNumber = 1 GROUP BY userID HAVING COUNT(*) >= 103)
SELECT schedule, COUNT(*) AS N, APPROX_QUANTILES(accuracy, 10) AS quantiles FROM withAccuracies GROUP BY schedule ORDER BY schedule