WITH
successes AS (SELECT *, MIN(timestamp) OVER (PARTITION BY cardID, userID ORDER BY timestamp ASC) AS firstTimestamp, MAX(timestamp) OVER (PARTITION BY cardID, userID) AS maxTimestamp FROM `logs.reviews` WHERE isRetry IS NOT TRUE AND reviewMarking = "remembered"),
withBestInterval AS (SELECT r.userID AS userID, r.cardID AS cardID, r.timestamp AS timestamp, ANY_VALUE(r.beforeInterval) AS beforeInterval, ANY_VALUE(r.reviewMarking) AS reviewMarking, MAX(s.beforeInterval) AS priorBestInterval, ANY_VALUE(s.firstTimestamp) AS firstTimestamp, ANY_VALUE(s.maxTimestamp) AS maxTimestamp FROM `logs.reviews` AS r JOIN successes AS s ON (r.cardID = s.cardID AND r.userID = s.userID AND r.timestamp > s.timestamp) WHERE r.isRetry IS NOT TRUE GROUP BY r.userID, r.cardID, r.timestamp),
withLapses AS (SELECT *, reviewMarking="forgotten" AND LAG(reviewMarking) OVER (PARTITION BY userID, cardID, reviewInterval ORDER BY timestamp) != "forgotten" AND beforeInterval > reviewInterval AS isLapseStart FROM withBestInterval JOIN (SELECT reviewInterval FROM `logs.validIntervals`) ON (withBestInterval.priorBestInterval >= reviewInterval) WHERE TIMESTAMP_DIFF(timestamp, firstTimestamp, DAY) < 365 AND TIMESTAMP_DIFF(maxTimestamp, firstTimestamp, DAY) >= 365),
samples AS (SELECT a.userID, a.cardID, a.timestamp, a.reviewInterval, ANY_VALUE(a.isLapseStart) AS isLapseStart, IF(MIN(b.timestamp) IS NULL, NULL, IF(MIN(c.timestamp) IS NULL, FALSE, MIN(c.timestamp) < MIN(b.timestamp))) AS isBackslide FROM (SELECT * FROM withLapses WHERE isLapseStart IS TRUE) AS a LEFT JOIN withLapses AS b ON (a.cardID = b.cardID AND a.userID = b.userID AND b.timestamp > a.timestamp AND b.reviewMarking = "remembered") FULL JOIN withLapses AS c ON (a.cardID = c.cardID AND a.userID = c.userID AND c.timestamp > a.timestamp AND c.reviewMarking = "forgotten" AND c.beforeInterval <= a.reviewInterval) GROUP BY a.userID, a.cardID, a.timestamp, a.reviewInterval)
SELECT reviewInterval, COUNTIF(isBackslide IS TRUE) AS backslideCount, COUNT(*) AS lapseCount, COUNT(DISTINCT userID) AS userCount FROM samples WHERE isBackslide IS NOT NULL GROUP BY reviewInterval