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 cardID, COUNT(*) as backslideCount, ANY_VALUE(essayName) AS essayName, ANY_VALUE(question) AS question FROM samples JOIN `logs.latestEssaysCards` USING (cardID) WHERE reviewInterval = 432000000 AND isBackslide IS TRUE GROUP BY carDID ORDER BY backslideCount DESC