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
  rev_actor
HAVING
  COUNT(*) <= 13
),
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
    UNION ALL
    SELECT
      DISTINCT actor_id
    FROM
      recentchanges_userindex AS recentchanges
      INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id
    WHERE
      rc_namespace = 4 -- Wikipedia:
      AND rc_title LIKE 'GLAM/%'
      AND rc_type = 0
      AND actor_user IS NOT NULL
)
SELECT ac_gamers.actor_id
FROM ac_gamers
LEFT OUTER JOIN wikied_actors ON actor.actor_id = ac_gamers.actor_id
WHERE wikied_actors.actor_id IS NULL
SQL

Checking query status...
