AC Gaming Weekdays

SQL

SET
  STATEMENT max_statement_time = 60 FOR
WITH first_creations AS (
  SELECT
    actor_id AS first_creation_actor,
    MIN(rc_timestamp) AS first_creation_timestamp
  FROM
    actor_recentchanges AS actor
    INNER JOIN user ON actor.actor_user = user.user_id
    INNER JOIN recentchanges_userindex AS recentchanges ON recentchanges.rc_actor = actor.actor_id
  WHERE
    TRUE
    AND user_registration > '20211101000000'
    AND (
      -- new page creations or moves
      rc_source = 'mw.new'
      OR (
        rc_source = 'mw.log'
        AND rc_log_type = 'move'
      )
    )
    AND rc_namespace = 0
  GROUP BY
    actor_id
),
ac_gamers AS (
SELECT
  rev_actor AS actor_id,
  COUNT(*)
FROM
  revision_userindex AS revision
  INNER JOIN first_creations ON revision.rev_actor = first_creations.first_creation_actor
WHERE
  TRUE
  AND rev_timestamp > '20211101000000'
  AND rev_timestamp < first_creation_timestamp
GROUP BY
  actor_id
HAVING
  COUNT(*) <= 13
),
rev_counts AS (
  SELECT
    rev_actor AS actor_id,
    COUNT(*) AS total,
    SUM(
      CASE
        WHEN day = 1 THEN 0
        WHEN day = 7 THEN 0
        ELSE 1
      END
    ) AS weekdays,
    COUNT(DISTINCT day) AS dist_days,
    COUNT(DISTINCT hour) AS dist_hours
  FROM
    (
      SELECT
        rev_actor,
        DAYOFWEEK(rev_timestamp) AS day,
        HOUR(rev_timestamp) AS hour
      FROM
        revision_userindex AS revision
      WHERE
        TRUE
        AND rev_actor IS NOT NULL
        AND rev_actor != 0
    ) AS q
  GROUP BY
    rev_actor
)
SELECT ac_gamers.actor_id
FROM ac_gamers
INNER JOIN rev_counts ON rev_counts.actor_id = ac_gamers.actor_id
WHERE
TRUE
AND rev_counts.total * 0.95 <= rev_counts.weekdays
SQL

Checking query status...
