WITH
agUsers AS (SELECT userID FROM `logs.registeredUsers` WHERE schedule="aggressiveStart"),
review AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS repetition FROM `logs.reviews` WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") AND beforeInterval=1000*60*60*24*5 AND sessionID IS NOT NULL AND isRetry IS NOT TRUE),
firstRepetitions AS (SELECT * FROM review WHERE repetition = 1),
validUsers AS (SELECT userID FROM firstRepetitions GROUP BY userID HAVING COUNT(*) = 112)
SELECT userID, cardID, IF(reviewMarking="remembered", 1, 0) AS result FROM firstRepetitions JOIN validUsers USING (userID) ORDER BY userID, cardID
-- users AS (SELECT DISTINCT userID FROM `logs.reviews` JOIN agUsers USING (userID) WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") GROUP BY userID HAVING COUNT(DISTINCT cardID)=112),
-- withRank AS (SELECT *, RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS repetition FROM `logs.reviews` JOIN users USING (userID) WHERE cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc") ),
-- r AS (SELECT userID, cardID, IF(reviewMarking="remembered", 1, 0) AS result FROM withRank WHERE repetition = 1 ORDER BY userID, cardID)