20220117114202

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 (beforeInterval IS NULL OR beforeInterval <= 1000*60*60*24) AND cardID IN (SELECT cardID FROM `logs.latestEssaysCards` WHERE essayName="qcvc"))

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