20210203103726

WITH

reviews AS (SELECT *, COUNTIF(reviewMarking="remembered") OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS correctCount, TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, sessionID ORDER BY timestamp), MILLISECOND) AS duration, TIMESTAMP_DIFF(MAX(timestamp) OVER (PARTITION BY userID, cardID), MIN(timestamp) OVER (PARTITION BY userID, cardID), DAY) AS reviewSpan FROM `logs.reviews`),

eligiblePaths AS (SELECT cardID, userID FROM reviews WHERE reviewSpan >= 90),

samples AS (SELECT * FROM reviews JOIN eligiblePaths USING (cardID, userID))

SELECT correctCount, APPROX_QUANTILES(duration / 1000, 4), COUNT(*) AS N FROM samples GROUP BY correctCount ORDER BY correctCount ASC