20211216124546

WITH
users AS (SELECT DISTINCT userID FROM `logs.reviews` 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)

SELECT * FROM r