20210319145756

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, AVG(repetitionCount) AS averageRepetitions FROM userSamples GROUP BY cardID ORDER BY averageRepetitions