SELECT rc_title, rc_actor, COUNT(*) AS nb_revocations, COUNT(DISTINCT rc_actor) as nb_users_revocated, MAX(recentchanges.rc_timestamp)

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

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

GROUP BY recentchanges.rc_title

# With at least 4 differents actors revocated (if it's only one person, better ask for an account ban)

HAVING COUNT(DISTINCT rc_actor) > 3

    # With at least 4 revocations

    AND COUNT(*) >= 3

    # And check if the last revocation was in the 2 last days, so that if we ask for a protection and it was given for 3 days,

    # we don't ask for a new protection just at the end of the last one

    AND MAX(recentchanges.rc_timestamp) >= curdate() - interval 2 day