WITH
reviews AS (SELECT *, 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 = "xiNW1zgeb2ITHGi6uQtg"),
samples AS (SELECT * FROM reviews WHERE reviewCount = 3 AND correctCount = 2)
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