20210318153556

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(["oIMu3gbdVLkFIV34QIWc",
"uWWZMohqS3QwXNwuYqxG",
"g9esyEGiKjGkALKEgHJZ",
"HlsdLBJ1DBfS78pRtOlR",
"NMWpX0WeuOc1LjM3osNW",
"ZnE6DbC1IIYYvXnWCNZ0",
"cBb4ybvcMbhOM0EEDjSD",
"ufNl3k80mTcfqIzViUeh",
"3N1VPeantqZvp40IjRkx",
"okJcmViU4gpFi42ZUNlO",
"lvLVFSDJzSpPmmNFs0Bq",
"DMj0jKGaMEffwCdel9Ok",
"euypxIVzFA7YB2Tlxra8",
"szdjemSOQFeXNicZ92jm",
"KauR2SOrJtvoEBJTaVK0",
"NB5XDC32OqTuhMVkUGZd",
"pv8GQ2NpXHauq7dFQu4i",
"7JcRxC0W1JD6iCahfGnt",
"LFA9ZWkL4cnkt1ijMjeg",
"Bn1qdaoscsyKn20YcbRy",
"pcE6FfFpCHh2sOR0oVt8",
"oOyDK2pZjVac1nhC5fYy",
"dyYV5qf5yBByn0j6SqWX",
"OF0GcNjR7YkOK1BLFAyW",
"r0aPQB8qnSUWV0VFZVnP",
"uoXnvdHlLPCeHnlx9zkD",
"bS3XyvMqqsailbuPMnDR",
"6ovcP28jH0upWr7kP4I9",
"IYx24YvxiL1e1KB2vnbI",
"0swi5xdrCEZFiALeyILN",
"E6DaxVj2NOEiS0jcGcih",
"0LBqNCdJS6UIu5qEbPjI",
"tl4BavGlVOdM4Gta2Qlc",
"oEceJqT6i3VlX5R0vyjT",
"a9VcX6HSJipD33XK1aKr",
"00nCnXYzUol4tTRDwJMt",
"adumnXmObq4O5kGgywRv",
"eugu7LKeiO34xF1ParBu",
"YF8egyfnEnwt9eSVcRq3",
"saK28MyBHCsT9nc7N49u",
"VvKSgUzXwzfNRJFsYyjE",
"5e3YiL6Siz0bV4iJaAvW",
"08deMUs0bTwmpWc6ejVs",
"ehkGiOMSBG55jTiUVzC9",
"THZdeQbFFriDCeLG8NVS",
"gpGgIAZi09YHv6Ox71V2",
"h1AXHXVtsGKxkamS8Hb2",
"FMrViiSuZsNOvSniHT0i",
"E4g8wyr2folQ87Slf3ZO",
"T6Yd6zYKXQ4ioUEjvLu7",
"mI5Sf36iMrDKMpdFHdWU",
"tO1x9HikrkiI22JYX8PQ",
"CvWy7vLttDaNz3fhpGhe",
"E41tvDzjkhJf7jbzU6qL"])
GROUP BY
  delta
ORDER BY
  delta