Week days (2) - Quarry

SQL

SET
  STATEMENT max_statement_time = 900 FOR
WITH rc_counts AS (
  SELECT
    rc_actor,
    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
        rc_actor,
        DAYOFWEEK(rc_timestamp) AS day,
        HOUR(rc_timestamp) AS hour
      FROM
        recentchanges_userindex AS recentchanges
      WHERE
        rc_ip IS NULL
        AND rc_bot = 0
        AND rc_type = 0 -- mw.edit (excludes mw.log, mw.categorize, etc)
    ) AS q
  GROUP BY
    rc_actor
),
rev_counts AS (
  SELECT
    rev_actor,
    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
),
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
  actor.actor_name,
  rev_counts.total,
  rev_counts.weekdays,
  rev_counts.dist_days
FROM
  rc_counts
  INNER JOIN actor_recentchanges AS actor ON rc_counts.rc_actor = actor.actor_id
  INNER JOIN user ON actor.actor_user = user.user_id
  INNER JOIN rev_counts ON rc_counts.rc_actor = rev_counts.rev_actor
  LEFT OUTER JOIN ipblocks ON ipblocks.ipb_address = actor.actor_name
  LEFT OUTER JOIN wikied_actors ON actor.actor_id = wikied_actors.actor_id
WHERE
  -- Contribs and distribution
  rc_counts.total >= 20
  AND rc_counts.total = rc_counts.weekdays
  AND rc_counts.dist_days >= 4
  AND rc_counts.dist_hours <= 10
  AND rev_counts.total >= 20
  AND rev_counts.total < 500
  AND rev_counts.total * 0.98 <= rev_counts.weekdays 
  AND rev_counts.dist_days >= 5
  AND rev_counts.dist_hours <= 10
  -- Further constraint to registered users
  AND actor_user IS NOT NULL
  -- Filter out some unmarked bots
  AND actor_name NOT LIKE '%Bot'
  AND actor_name NOT LIKE '% (BEIC)'
  AND actor_name NOT LIKE '%LIUC%'
  AND wikied_actors.actor_id IS NULL
  AND ipb_address IS NULL
ORDER BY
  dist_days ASC, total DESC
SQL

Checking query status...
