Early gaming - experiment

SQL

SET
  STATEMENT max_statement_time = 120 FOR
WITH wikied_actors AS (
  SELECT
      DISTINCT actor_id
    FROM
      change_tag
      INNER JOIN revision_userindex AS revision ON change_tag.ct_rev_id = revision.rev_id
      INNER JOIN actor_revision AS actor ON revision.rev_actor = actor.actor_id
    WHERE
      ct_tag_id = 530 -- WikiEdu: OAuth CID: 1232
      AND actor_user IS NOT NULL
)
SELECT
  main_q.actor_id,
  actor_name,
  edit_count,
  non_empty_count
FROM
  (
    SELECT
      actor_id,
      actor_name,
      COUNT(*) AS edit_count,
      SUM(
        LENGTH(
          TRIM(
            REGEXP_REPLACE(comment_text, '/\\* .* \\*/ *', '')
          )
        ) > 0
      ) AS non_empty_count,
      SUM(rc_title LIKE 'GLAM/%') is_glam,
      SUM(comment_text LIKE '%The Wikipedia Adventure%') AS is_twa,
      SUM(comment_text LIKE '%#1lib1ref%') AS is_event,
      SUM(DAYOFWEEK(rc_timestamp) IN (2, 3, 4, 5, 6)) AS weekday_edit_count,
      COUNT(DISTINCT DAYOFYEAR(rc_timestamp)) AS distinct_days
    FROM
      recentchanges_userindex AS recentchanges
      INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id
      INNER JOIN user ON actor.actor_user = user.user_id
      INNER JOIN comment_recentchanges AS comment ON recentchanges.rc_comment_id = comment.comment_id
    WHERE
      TRUE
      AND rc_source IN ('mw.edit')
      AND rc_bot = 0
      AND actor_user IS NOT NULL
      AND user_registration >= "20220212000000"
      AND user_editcount < 500
    GROUP BY
      actor_id
    HAVING
      edit_count < 500
      AND edit_count > 30
      AND non_empty_count / edit_count >= 0.95
      AND weekday_edit_count / edit_count >= 0.6
      AND distinct_days > 1
      AND is_twa / edit_count < 0.7
      AND is_event = 0
      AND is_glam = 0
  ) AS main_q
  LEFT OUTER JOIN ipblocks ON actor_name = ipblocks.ipb_address
  LEFT OUTER JOIN wikied_actors ON main_q.actor_id = wikied_actors.actor_id
WHERE
  TRUE
  AND ipb_address IS NULL
  AND wikied_actors.actor_id IS NULL
SQL

Checking query status...
