20211116182559

WITH
  users AS (
  SELECT
    *
  FROM
    `logs.registeredUsers`),
  initiallyForgotten AS (
  SELECT
    DISTINCT userID,
    schedule,
    cardID
  FROM
    `logs.reviews`
  JOIN
    `users`
  USING
    (userID)
  WHERE
    cardID IN UNNEST([
“THZdeQbFFriDCeLG8NVS”,
“3pzDWNJyk9c4SkCtreNN”,
“g9esyEGiKjGkALKEgHJZ”,
“bS3XyvMqqsailbuPMnDR”,
“DMj0jKGaMEffwCdel9Ok”,
“adumnXmObq4O5kGgywRv”,
“pv8GQ2NpXHauq7dFQu4i”,
“6ovcP28jH0upWr7kP4I9”,
“OF0GcNjR7YkOK1BLFAyW”,
“Bn1qdaoscsyKn20YcbRy”
    ])
   ),
  subsequentReviews AS (
  SELECT
    *,
    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 OR schedule=“original”))
SELECT
  beforeInterval / (1000*60*60*24),
  COUNT(DISTINCT userID) AS N,
  COUNT(*) AS promptN,
  COUNTIF(reviewMarking=“remembered”) / COUNT(*) AS accuracy
FROM
  subsequentReviews
WHERE
  fullReviewCount = 1
GROUP BY
  beforeInterval
ORDER BY
  beforeInterval