20211123171811

WITH
  users AS (
  SELECT
    * EXCEPT (timestamp), timestamp as registrationTimestamp
  FROM
    `logs.registeredUsers` WHERE schedule != "original" AND schedule != "aggressiveStart"),
  initialReviews AS (
  SELECT
    DISTINCT userID,
    registrationTimestamp,
    schedule,
    cardID
  FROM
    (SELECT * EXCEPT (reviewCount), RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS reviewCount FROM `logs.reviews`)
  JOIN
    `users`
  USING
    (userID)
  WHERE
    sessionID IS NULL AND reviewCount = 1 AND reviewMarking = "remembered" AND
    cardID IN(
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc")
   ),
  subsequentReviews AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS realReviewCount
  FROM
    `logs.reviews`
  JOIN
    initialReviews
  USING
    (userID,
      cardID)
  WHERE
    sessionID IS NOT NULL AND isRetry IS NOT TRUE),
  firstSession AS (
    SELECT userID, cardID, beforeInterval, reviewMarking FROM subsequentReviews WHERE realReviewCount = 1 AND beforeInterval>1000*60*60*24 AND reviewMarking="remembered"
  ),
  secondSession AS (
    SELECT subsequentReviews.* FROM subsequentReviews JOIN firstSession USING (userID, cardID) WHERE subsequentReviews.realReviewCount = 2
  ),
  thirdSession AS (
      SELECT subsequentReviews.* FROM subsequentReviews JOIN secondSession USING (userID, cardID) WHERE subsequentReviews.realReviewCount = 3 AND subsequentReviews.beforeInterval=1000*60*60*24 AND subsequentReviews.reviewMarking="remembered"
  ),
  fourthSession AS (
    SELECT subsequentReviews.* FROM subsequentReviews JOIN thirdSession USING (userID, cardID) WHERE subsequentReviews.realReviewCount = 4 AND subsequentReviews.beforeInterval>1000*60*60*24
  )


SELECT
firstSession.beforeInterval / (1000*60*60*24) AS firstDays,
secondSession.beforeInterval / (1000*60*60*24) AS secondDays,
--   SUBSTR(schedule, 0, 9) AS schedule,
  COUNT(DISTINCT userID) AS N,
  COUNT(*) AS promptN,
  COUNTIF(reviewMarking="remembered") / COUNT(*) AS accuracy
FROM
    secondSession JOIN (SELECT userID, beforeInterval, cardID FROM firstSession) AS firstSession USING (userID, cardID)
GROUP BY
firstDays, secondDays
ORDER BY
firstDays, secondDays