Week days (2) - Quarry

SQL

SET
  STATEMENT max_statement_time = 600 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
  FROM
    (
      SELECT
        rc_actor,
        DAYOFWEEK(rc_timestamp) AS day
      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
  FROM
    (
      SELECT
        rev_actor,
        DAYOFWEEK(rev_timestamp) AS day
      FROM
        revision_userindex AS revision
      WHERE
        TRUE
        AND rev_actor IS NOT NULL
        AND rev_actor != 0
    ) AS q
  GROUP BY
    rev_actor
)
SELECT
  user_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
WHERE
  -- Contribs and distribution
  rc_counts.total >= 40
  AND rc_counts.total = rc_counts.weekdays
  AND rc_counts.dist_days >= 5
  AND rev_counts.total >= 40
  AND rev_counts.total * 0.85 <= rev_counts.weekdays 
  AND rev_counts.dist_days >= 5
  -- Further constraint to registered users
  AND actor_user IS NOT NULL
  -- Filter out some unmarked bots
  AND user_name NOT LIKE '%Bot'
ORDER BY
  total DESC
SQL

Checking query status...
