WITH
scheduleUsers AS (SELECT userID FROM `logs.registeredUsers` WHERE schedule="aggressiveStart"),
qcvcCards AS (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc"),
withPreviousTimestamps AS (SELECT *, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS previousTimestamp FROM `logs.reviews` WHERE isRetry IS NOT TRUE),
reviews AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewNumber, MAX(IF(reviewMarking="remembered", TIMESTAMP_DIFF(timestamp, previousTimestamp, HOUR) / 24.0, NULL)) OVER (PARTITION BY userID, cardID ORDER BY timestamp ASC) AS demonstratedRetention FROM withPreviousTimestamps JOIN qcvcCards USING (cardID) JOIN scheduleUsers USING (userID)),
eligibleUsers AS (SELECT userID FROM reviews WHERE reviewNumber=6 GROUP BY userID HAVING COUNT(*) >= 103),
userSamples AS (SELECT userID, reviewNumber, APPROX_QUANTILES(demonstratedRetention, 100) AS centiles FROM reviews JOIN eligibleUsers USING (userID) GROUP BY userID, reviewNumber)
SELECT reviewNumber, COUNT(*) AS N, APPROX_QUANTILES(centiles[OFFSET(5)], 4) AS fifthPercentilePrompt, APPROX_QUANTILES(centiles[OFFSET(10)], 4) AS tenthPercentilePrompt, APPROX_QUANTILES(centiles[OFFSET(25)], 4) AS twentyFifthPercentilePrompt, APPROX_QUANTILES(centiles[OFFSET(50)], 4) AS medianPrompt FROM userSamples GROUP BY reviewNumber ORDER BY reviewNumber