WITH
attemptLogs AS (SELECT *, MIN(timestamp) OVER (PARTITION BY cardID, userID) AS firstTimestamp, MAX(timestamp) OVER (PARTITION BY cardID, userID) AS lastTimestamp, RANK() OVER (PARTITION BY cardID, userID ORDER BY timestamp) AS reviewNumber FROM `logs.reviews` WHERE isRetry IS NOT TRUE),
withLapsesInPeriod AS (SELECT *, COUNTIF(reviewMarking="forgotten" AND TIMESTAMP_DIFF(timestamp, firstTimestamp, DAY) < 180) OVER (PARTITION BY userID, cardID) AS lapseCount FROM attemptLogs WHERE TIMESTAMP_DIFF(lastTimestamp, firstTimestamp, DAY) >= 180),
samples AS (SELECT cardID, COUNTIF(reviewMarking = "remembered" AND lapseCount > 1) AS rememberedMultiLapse, COUNTIF(reviewMarking = "forgotten" AND lapseCount > 2) AS forgottenMultiLapse, COUNTIF(reviewMarking="remembered") AS remembered, COUNTIF(reviewMarking="forgotten") AS forgotten, ANY_VALUE(essayName) AS essayName, ANY_VALUE(question) AS question FROM withLapsesInPeriod JOIN `logs.latestEssaysCards` USING (cardID) WHERE reviewNumber = 2 GROUP BY cardID)
SELECT cardID, SAFE_DIVIDE(rememberedMultiLapse, remembered) AS cFraction, SAFE_DIVIDE(forgottenMultilapse, forgotten) AS nFraction, SAFE_DIVIDE(SAFE_DIVIDE(forgottenMultilapse,forgotten), rememberedMultilapse/remembered) AS likelihoodRatio, essayName, question FROM samples ORDER BY essayName, nFraction DESC