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
  actor_name
FROM
  ac_gamers
  INNER JOIN rev_counts ON rev_counts.actor_id = ac_gamers.actor_id
  INNER JOIN actor_recentchanges AS actor ON ac_gamers.actor_id = actor.actor_id
  LEFT OUTER JOIN ipblocks ON ipblocks.ipb_address = actor.actor_name
WHERE
  TRUE
  AND rev_counts.total * 0.95 <= rev_counts.weekdays
  AND rev_counts.dist_days >= 3
  AND ipb_address IS NULL
SQL

Checking query status...
