20210319144224

WITH

reviews AS (SELECT *, MIN(timestamp) OVER (PARTITION BY cardID, userID) AS firstTimestamp, MAX(timestamp) OVER (PARTITION BY cardID, userID) AS lastTimestamp FROM `logs.reviews` WHERE isRetry IS NOT TRUE),

eligibleReviews AS (SELECT * FROM reviews WHERE TIMESTAMP_DIFF(lastTimestamp, firstTimestamp, DAY) >= 360 AND TIMESTAMP_DIFF(timestamp, firstTimestamp, DAY) <= 360),

userSamples AS (SELECT userID, cardID, COUNT(*) AS repetitionCount FROM eligibleReviews GROUP BY cardID, userID)

SELECT cardID, COUNT(DISTINCT userID) AS userCount, APPROX_QUANTILES(repetitionCount, 4) AS repetitionCount FROM userSamples GROUP BY cardID ORDER BY repetitionCount[OFFSET(2)], repetitionCount[OFFSET(1)] + repetitionCount[OFFSET(2)] + repetitionCount[OFFSET(3)]