20220119122607

WITH
  users AS (
  SELECT
    * EXCEPT (timestamp), timestamp as registrationTimestamp
  FROM
    `logs.registeredUsers` WHERE schedule != "original" AND schedule != "aggressiveStart" AND timestamp >= TIMESTAMP("2021-04-19")),
  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
    cardID IN(
    SELECT
      cardID
    FROM
      `logs.latestEssaysCards`
    WHERE
      essayName="qcvc")
   ),
  subsequentReviews AS (
  SELECT
    *,
    RANK() OVER (PARTITION BY userID, cardID ORDER BY timestamp) AS realReviewCount,
    TIMESTAMP_DIFF(timestamp, LAG(timestamp) OVER (PARTITION BY userID, cardID ORDER BY timestamp), HOUR) AS delay
  FROM
    `logs.reviews`
  JOIN
    initialReviews
  USING
    (userID,
      cardID)
  WHERE
    sessionID IS NOT NULL AND isRetry IS NOT TRUE AND beforeInterval > 1000*60*60*24)

SELECT COUNT(*), COUNT(DISTINCT userID) FROM subsequentReviews WHERE beforeInterval=60*1000*60*60*24 AND realReviewCount=1