Weekdays FJ - Quarry

SQL

SET
  STATEMENT max_statement_time = 900 FOR
WITH rc_counts AS (
  SELECT
    rc_actor,
    actor_name,
    COUNT(*) AS total,
    SUM(
      CASE
        WHEN day = 6 THEN 0
        WHEN day = 7 THEN 0
        ELSE 1
      END
    ) AS weekdays,
    SUM(
      CASE
        WHEN hour >= 12 AND hour <= 24 THEN 1
        ELSE 0
      END
    ) AS in_schedule_hours,
    COUNT(DISTINCT day) AS dist_days,
    COUNT(DISTINCT hour) AS dist_hours,
    COUNT(DISTINCT hour_day) AS dist_hour_days
  FROM
    (
      SELECT
        rc_actor,
        actor_name,
        DAYOFWEEK(rc_timestamp) AS day,
        HOUR(rc_timestamp) AS hour,
        CONCAT(DAYOFWEEK(rc_timestamp), HOUR(rc_timestamp)) AS hour_day
      FROM
        recentchanges_userindex AS recentchanges
        INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id
      WHERE
        TRUE
        AND rc_bot = 0
        AND rc_type = 0 -- mw.edit (excludes mw.log, mw.categorize, etc)
        AND actor_user >= 26787120
        AND actor_user IS NOT NULL
    ) AS q
  GROUP BY
    rc_actor
),
rev_counts AS (
  SELECT
    rev_actor,
    COUNT(*) AS total,
    SUM(
      CASE
        WHEN day = 6 THEN 0
        WHEN day = 7 THEN 0
        ELSE 1
      END
    ) AS weekdays,
    SUM(
      CASE
        WHEN hour >= 12 AND hour <= 24 THEN 1
        ELSE 0
      END
    ) AS in_schedule_hours,
    COUNT(DISTINCT day) AS dist_days,
    COUNT(DISTINCT hour) AS dist_hours,
    COUNT(DISTINCT hour_day) AS dist_hour_days
  FROM
    (
      SELECT
        rev_actor,
        DAYOFWEEK(rev_timestamp) AS day,
        HOUR(rev_timestamp) AS hour,
        CONCAT(DAYOFWEEK(rev_timestamp), HOUR(rev_timestamp)) AS hour_day
      FROM
        revision_userindex AS revision
        INNER JOIN actor_revision AS actor ON revision.rev_actor = actor.actor_id
      WHERE
        TRUE
        AND actor_user >= 26787120
        AND actor_user IS NOT NULL
    ) AS q
  GROUP BY
    rev_actor
)
SELECT
  rc_counts.actor_name,
  rev_counts.total,
  rev_counts.weekdays,
  rev_counts.dist_days
FROM
  rc_counts
  INNER JOIN rev_counts ON rc_counts.rc_actor = rev_counts.rev_actor
WHERE
  TRUE
  -- Contribs and distribution
  AND rc_counts.total >= 10
  AND rc_counts.total * 0.7 <= rc_counts.weekdays
  AND rc_counts.total * 0.8 <= rc_counts.in_schedule_hours
  AND rc_counts.dist_days >= 2
  AND rc_counts.dist_hours <= 11
  AND rc_counts.dist_hours >= 4
  
  aND rev_counts.total >= 20
  AND rev_counts.total * 0.8 <= rev_counts.weekdays
  AND rev_counts.total * 0.9 <= rev_counts.in_schedule_hours
  AND rev_counts.dist_days >= 5
  AND rev_counts.dist_hours <= 11
  AND rev_counts.dist_hours >= 4
  AND rev_counts.dist_hour_days >= 30
ORDER BY
  dist_days ASC, total DESC
SQL

Checking query status...
