20220117114610

WITH
users AS (
SELECT
*
FROM
`logs.registeredUsers`
WHERE
schedule = "original"),

withSessionNumber AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS sessionNumber FROM `logs.reviews` WHERE sessionID IS NOT NULL AND isRetry IS NOT TRUE AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc"))

SELECT cardID, COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy, COUNT(*) AS reviewCount, COUNT(DISTINCT userID) AS userCount FROM withSessionNumber WHERE sessionNumber = 1 AND beforeInterval=1000*60*60*24 GROUP BY cardID ORDER BY accuracy