20220208113105

WITH
reviews AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber FROM (SELECT *, 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 beforeInterval >= 1000*60*60*48),
samples AS (SELECT * FROM reviews WHERE reviewNumber = 1 AND beforeInterval=1000*60*60*24*30)

SELECT ROUND(delay/7)*7 AS delayBucket, COUNTIF(reviewMarking="remembered")/COUNT(*) AS recallRate, COUNT(*) AS N FROM samples WHERE cardID="eugu7LKeiO34xF1ParBu" GROUP BY delayBucket ORDER BY delayBucket