WITH
successes AS (SELECT * 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 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),
traces AS (SELECT cardID, userID, reviewInterval, COUNTIF(beforeInterval <= reviewInterval AND reviewMarking="forgotten") AS backslideCount FROM withBestInterval JOIN (SELECT reviewInterval FROM `logs.validIntervals`) ON (withBestInterval.priorBestInterval >= reviewInterval) GROUP BY cardID, userID, reviewInterval)
SELECT reviewInterval, backslideCount, COUNT(*) AS traceCount, COUNT(DISTINCT userID) as userCount FROM traces GROUP BY reviewInterval, backslideCount ORDER BY reviewInterval, backslideCount