20211126112016

WITH
  users AS (
  SELECT
    * EXCEPT (timestamp), timestamp as registrationTimestamp
  FROM
    `logs.registeredUsers`),
  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 reviewMarking="remembered"
  ),
  secondSession AS (
    SELECT subsequentReviews.* FROM subsequentReviews JOIN firstSession USING (userID, cardID) WHERE subsequentReviews.realReviewCount = 2 AND subsequentReviews.reviewMarking="remembered"
  ),
  thirdSession AS (
      SELECT subsequentReviews.* FROM subsequentReviews JOIN secondSession USING (userID, cardID) WHERE subsequentReviews.realReviewCount = 3
  ),
  fourthSession AS (
    SELECT subsequentReviews.* FROM subsequentReviews JOIN thirdSession USING (userID, cardID) WHERE subsequentReviews.realReviewCount = 4
  )


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