Users highly revocated

SQL

SELECT actor.*, count(actor_rc.ctd_name) / count(*)  AS ratio_revocation, COUNT(*) as nb_editions, to_inspect_actors.last_revocation
FROM (SELECT recentchanges.rc_actor as actor_id, MAX(recentchanges.rc_timestamp) AS last_revocation
  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_recentchanges AS 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(*) > 2) as to_inspect_actors
JOIN (SELECT rc_actor, ctd_name
      FROM recentchanges_userindex as 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 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
JOIN user ON actor.actor_name = user.user_name

LEFT JOIN ipblocks_ipindex AS ipblocks on actor.actor_name = ipblocks.ipb_address
WHERE ipblocks.ipb_id IS NULL AND user.user_registration > curdate() - interval 7 day
GROUP BY to_inspect_actors.actor_id
HAVING count(actor_rc.ctd_name) / count(*) > 0.8
ORDER BY to_inspect_actors.last_revocation DESC
SQL

Checking query status...
