20211116172650

WITH
  users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`
  WHERE
    schedule NOT IN UNNEST([“original”, “aggressiveStart”])),
  initiallyForgotten AS (
  SELECT
    DISTINCT userID,
    cardID
  FROM
    `logs.reviews`
  JOIN
    `users`
  USING
    (userID)
  WHERE
    reviewCount = 1
    AND cardID IN (
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName=“qcvc”)),
  subsequentReviews AS (
  SELECT
    *,
    beforeInterval / (1000*60*60*24) AS intervalDays,
    RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS fullReviewCount
  FROM
    `logs.reviews`
  JOIN
    initiallyForgotten
  USING
    (userID,
      cardID)
  WHERE
    sessionID IS NOT NULL
    AND beforeInterval > 1000*60*60*48),
cardStats AS (SELECT
  intervalDays,
  cardID,
  COUNT(DISTINCT userID) AS N,
  COUNT(*) AS promptN,
  COUNTIF(reviewMarking=“remembered”) / COUNT(*) AS accuracy
FROM
  subsequentReviews 
WHERE
  fullReviewCount = 1
GROUP BY
  cardID, intervalDays)

SELECT cardID, ANY_VALUE(l.accuracy) AS oneWeek, ANY_VALUE(r.accuracy) AS twoMonths, ANY_VALUE(r.N) AS twoMonthsN, ANY_VALUE(l.accuracy)-ANY_VALUE(r.accuracy) AS drop FROM (SELECT * FROM cardStats WHERE intervalDays=7) AS l JOIN (SELECT * FROM cardStats WHERE intervalDays=30) AS r USING (cardID) GROUP BY cardID