20220207122212

WITH

reviews AS (SELECT *, RANK() OVER (PARTITION BY cardID, userID ORDER BY timestamp) - 1 AS repetitionNumber, TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), DAY) AS delay FROM `logs.reviews` WHERE isRetry IS NOT TRUE AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc")),

inEssayRecallRates AS (SELECT userID, COUNT(*) AS cardCount, COUNTIF(reviewMarking="remembered")/COUNT(*) AS essayRecallRate FROM reviews WHERE repetitionNumber = 0 GROUP BY userID HAVING cardCount >= 50),

users AS (SELECT DISTINCT userID FROM inEssayRecallRates WHERE essayRecallRate >= 0.831 AND essayRecallRate <= 0.957)

SELECT repetitionNumber, cardID, SAFE_DIVIDE(COUNTIF(delay>=30), COUNT(*)) AS eligibleFraction, SAFE_DIVIDE(COUNTIF(delay>=30 AND reviewMarking="remembered"), COUNTIF(delay>=30)) AS successfulFraction, COUNT(*) AS N FROM reviews JOIN users USING (userID) WHERE cardID="17fsggSIuqxnhKFwOI2g" GROUP BY repetitionNumber, cardID ORDER BY repetitionNumber, cardID