Timing co-occurrences

SQL

SET STATEMENT max_statement_time = 90 FOR
WITH relevant_hours AS (
    SELECT
    DISTINCT (rc_timestamp DIV 10000) AS hour
    FROM
      recentchanges_userindex AS recentchanges
      INNER JOIN change_tag ON recentchanges.rc_id = change_tag.ct_rc_id
      INNER JOIN change_tag_def ON change_tag.ct_tag_id = change_tag_def.ctd_id
      INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id
    WHERE
      TRUE
      AND rc_type = 0
      AND rc_namespace = 0
      AND rc_bot = 0
      AND actor_user IS NOT NULL
      AND change_tag_def.ctd_name = 'visualeditor'
      AND rc_timestamp >= (CURRENT_DATE - INTERVAL 14 DAY)
      AND actor_name IN ('IvoryRichards', 'AshannaMyr', 'MikaPotterx')
  ),
  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
        INNER JOIN change_tag ON recentchanges.rc_id = change_tag.ct_rc_id
        INNER JOIN change_tag_def ON change_tag.ct_tag_id = change_tag_def.ctd_id
      WHERE
        rc_ip IS NULL
        AND rc_bot = 0
        AND rc_type = 0 -- mw.edit (excludes mw.log, mw.categorize, etc)
        AND change_tag_def.ctd_name = 'visualeditor'
    ) AS q
  GROUP BY
    rc_actor
  HAVING weekdays = total AND dist_days >= 3
),
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
)
SELECT
  actor_name,
  COUNT(DISTINCT rc_timestamp DIV 10000) AS overlap_hours
FROM
  recentchanges_userindex AS recentchanges
  INNER JOIN change_tag ON recentchanges.rc_id = change_tag.ct_rc_id
  INNER JOIN change_tag_def ON change_tag.ct_tag_id = change_tag_def.ctd_id
  INNER JOIN actor_recentchanges AS actor ON recentchanges.rc_actor = actor.actor_id
WHERE
  TRUE
  AND rc_type = 0
  AND rc_namespace = 0
  AND rc_bot = 0
  AND actor_user IS NOT NULL
  AND change_tag_def.ctd_name = 'visualeditor'
  AND rc_timestamp >= (CURRENT_DATE - INTERVAL 14 DAY)
  AND (
    (rc_timestamp DIV 10000) IN (SELECT hour FROM relevant_hours)
    OR ((rc_timestamp DIV 10000) + 1) IN (SELECT hour FROM relevant_hours)
    OR ((rc_timestamp DIV 10000) - 1) IN (SELECT hour FROM relevant_hours)
  )
  AND rc_actor IN (SELECT rc_actor FROM rc_counts)
  AND rc_actor NOT IN (SELECT actor_id FROM wikied_actors)
GROUP BY actor_name
HAVING overlap_hours >= 3
ORDER BY overlap_hours DESC;
SQL

Checking query status...
