Early gaming - experiment

SQL

SET
  STATEMENT max_statement_time = 120 FOR
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,
      SUM(rc_namespace = 118) AS draft_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', 'mw.new')
      AND rc_bot = 0
      AND actor_user IS NOT NULL
      AND user_registration >= "20220301000000"
      AND user_editcount < 50
    GROUP BY
      actor_id
    HAVING
      edit_count < 50
      AND edit_count > 5
      AND draft_edit_count / edit_count >= 0.4
      AND weekday_edit_count / edit_count >= 0.6
      AND distinct_days > 1
      AND is_twa / edit_count < 0.1
      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...
