WITH
reviews AS (SELECT * EXCEPT (reviewCount), RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewCount, COUNTIF(reviewMarking="remembered") OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS correctCount, TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), DAY) AS delay FROM `logs.reviews` WHERE cardID = "1ENYKL02RT8aidhI917F" AND isRetry IS NOT TRUE),
forgotInEssay AS (SELECT userID FROM reviews WHERE reviewCount = 1 AND reviewMarking="forgotten"),
samples AS (SELECT * FROM reviews JOIN forgotInEssay USING (userID) WHERE reviewCount = 3 AND correctCount = 1)
-- SELECT * FROM reviews JOIN forgotInEssay USING (userID) ORDER BY userID, timestamp
-- SELECT userID FROM samples WHERE delay <= 14
-- SELECT * FROM `logs.reviews` WHERE userID="KfyL2UmMkPZ7zYLZaUDSXRr4uUJ2" AND cardID="xiNW1zgeb2ITHGi6uQtg" ORDER BY timestamp ASC
SELECT beforeInterval, ROUND(delay/7)*7 AS dayBucket, COUNTIF(reviewMarking="remembered")/COUNT(*) AS recallRate, COUNT(*) AS N FROM samples GROUP BY beforeInterval, dayBucket HAVING N >= 10 ORDER BY beforeInterval, dayBucket