Users highly revocated

SQL

SELECT actor.*, count(actor_rc.ctd_name) / count(*)  AS ratio_revocation
FROM (SELECT recentchanges.rc_actor as actor_id
  FROM recentchanges
      JOIN change_tag ON ct_rev_id = recentchanges.rc_this_oldid
      JOIN change_tag_def ON change_tag_def.ctd_id = change_tag.ct_tag_id
      JOIN actor ON recentchanges.rc_actor = actor.actor_id
  LEFT JOIN page_restrictions on recentchanges.rc_cur_id = page_restrictions.pr_page

  WHERE rc_namespace = 0
      # Get only recent rc
      AND recentchanges.rc_timestamp >= curdate() - interval 4 day
      # Don't count autopatrolled reverted
      AND recentchanges.rc_patrolled != 2
      # Check if they have the 'reverted' tag, which is our way of knowing if they've been revocated
      AND change_tag_def.ctd_name = 'mw-reverted'
      # Don't fetch already protected pages
      AND page_restrictions.pr_id IS NULL
      AND actor.actor_user IS NOT NULL
  GROUP BY recentchanges.rc_actor
   HAVING COUNT(*) > 3) as to_inspect_actors
JOIN (SELECT rc_actor, ctd_name
      FROM recentchanges
      LEFT JOIN (SELECT *
                 FROM change_tag
                 JOIN change_tag_def ON change_tag_def.ctd_id = change_tag.ct_tag_id
                 WHERE change_tag_def.ctd_name = 'mw-reverted') AS ct_rev_id ON ct_rev_id = recentchanges.rc_this_oldid
      #LEFT JOIN (SELECT * FROM  change_tag_def WHERE change_tag_def.ctd_name = 'mw-reverted') AS change_tag_def  ON change_tag_def.ctd_id = change_tag.ct_tag_id
      JOIN actor ON recentchanges.rc_actor = actor.actor_id
  LEFT JOIN page_restrictions on recentchanges.rc_cur_id = page_restrictions.pr_page

  WHERE rc_namespace = 0
      # Get only recent rc
      #AND recentchanges.rc_timestamp >= curdate() - interval 4 day
      # Don't count autopatrolled reverted
      AND recentchanges.rc_patrolled != 2
      # Check if they have the 'reverted' tag, which is our way of knowing if they've been revocated
      #AND change_tag_def.ctd_name = 'mw-reverted'
       ) AS actor_rc ON actor_rc.rc_actor = to_inspect_actors.actor_id
JOIN actor ON actor.actor_id = to_inspect_actors.actor_id
GROUP BY to_inspect_actors.actor_id
SQL

Checking query status...
