20210318153424

WITH
  eligibleUsers AS (
  SELECT
    userID
  FROM
    `logs.reviews`
  GROUP BY
    userID
  HAVING
    TIMESTAMP_DIFF(MAX(timestamp), MIN(TIMESTAMP), DAY) >= 31),
  reviews AS (
  SELECT
    *,
    COUNTIF(isRetry IS NOT TRUE) OVER (PARTITION BY userID, cardID ORDER BY timestamp ASC) AS reviewNumber,
    COUNTIF(reviewMarking = "forgotten") OVER (PARTITION BY userID, cardID ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lapses,
    FIRST_VALUE(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp ASC) AS firstTimestamp,
    TIMESTAMP_DIFF(LAG(nextDueTimestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp ASC), LAG(timestamp) OVER(PARTITION BY userID, cardID ORDER BY timestamp ASC), DAY) AS delta
  FROM
    `logs.reviews`
  WHERE
    userID IN (
    SELECT
      userID
    FROM
      eligibleUsers)),
  samples AS (
  SELECT
    *
  FROM
    reviews
  WHERE
    lapses = 0
    AND reviewNumber = 2
    AND firstTimestamp >= TIMESTAMP("2020-12-12")
    AND isRetry IS NOT TRUE)
    
SELECT
  COUNT(*) AS N,
  COUNTIF(reviewMarking="remembered")/COUNT(*) AS accuracy,
  delta,
FROM
  samples
WHERE cardID IN UNNEST(["184bRlF3iVYYvXomQpsL",
"xS1sny5dpoCj5zYIUdxM",
"VpMt0CzSgOqVrdtTReHa",
"AKlF96UW8bgpqEQkEVBn",
"sCR6RomX0y6hO1TfclMI",
"xU1NlPqrYsiCpUbh2G26",
"z7cOpsVhlVn3HJWB2VET",
"7n2cdUmwAsipMSiQEyJC",
"BCZEC16X1jeMuLGlrs92",
"3yWPmhfE9yV0ffkbiFTT",
"1iORGG8illPnVQltQ1kq",
"uUZf1VOOSdQxrjhtOQt1",
"kRYMWxmAfrlqqF0ZXR21",
"pa2ZDd3MLtAmXq0b3BwW",
"6NRxe1wnphNsMjTZ9LXf",
"3pzDWNJyk9c4SkCtreNN",
"RbRX14bgPwUiDlORcxQD",
"mvwUqFLSYcrZMvdgVtd0",
"Gn1r8zVV20zlqWh8gG1Y",
"SsT14eK8fWXNODox2M5U",
"xQte6vwqUwy2j5OWdxjK",
"C7PiMcLxFwHU081wYvwI",
"zRgw2osbesLEQ5ADo8DD",
"6H3CMpGxVyuLBaJKZGhI",
"wsfXIAGNrOJLlbaDgpiS",
"3VSipNciNbDKkFpzFeFp",
"bcbyBXRiH8lYkzIPuA6e",
"wFliehYIPjRrDY2mdvOr",
"G51BlvOBsvqToezS7BAg",
"4UJEzb4bQOqDlHcHzqRV",
"mRJYkVyrFikg9PzfWUgd",
"buvxh7iNAE0OGCWKLmlA",
"BzJJHouBDGHblwjbYsum",
"ONHWQIqjO2apC3D5Wq00",
"s8duZcGBbu0dxb4xEAGg",
"xiNW1zgeb2ITHGi6uQtg",
"NTt5NM7gqPotdxg3vUs0",
"7QM6pEJ9bxZHK50e1kww",
"OnKhM42b4nU5JAf76PxU",
"FeKW7VDf0bGodpNobATv",
"Sgl5VJI79NB6b22EqTn3",
"nmoNwDoCT7LymEi70EeT",
"nOJeZWGdHtSGTUMdX88v",
"HpQN6NxQs6Y8kdS6XOVY",
"S0ijLyYsCv8orVGWDhAd",
"pa4BhdkYjLrj7A022sA3",
"Az4W8dbzBXmiO4ZAQykL",
"Mh1cQe6i5bxHnkPsakEs",
"Dmvs7ezW9r77URieTjEh",
"ZYogO9HQAS9M4DkFSWh7",
"DsKjxLEnyp7KzC93t1YX",
"1ENYKL02RT8aidhI917F",
"17fsggSIuqxnhKFwOI2g",
"s4N3L6iTy93DK5F0mh6i",
"v3vTMn9bAA0joehFnsRK",
"44wRCJhM0SSLoyfFyhDs",
"LorT6689duDMfrlASd3O",
"UN7bsEiQmjXFQ9wsPjvL"])
GROUP BY
  delta
ORDER BY
  delta