This query is marked as a draft This query has been published by Ywats0ns.

SQL

AخA
 
SELECT recentchanges.rc_title AS rc_title, WEEK(recentchanges.rc_timestamp) as week, COUNT(*) as nb_revocations, COUNT(DISTINCT recentchanges.rc_actor) AS nb_actors
FROM recentchanges
    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 = 'R3R' ) AS change_tags_first ON change_tags_first.ct_rev_id = recentchanges.rc_this_oldid
    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 change_tags_second ON change_tags_second.ct_rev_id = recentchanges.rc_this_oldid
LEFT JOIN page_restrictions on recentchanges.rc_cur_id = page_restrictions.pr_page
WHERE rc_namespace = 0
    # Don't count autopatrolled reverted
    AND recentchanges.rc_patrolled != 2
    # Don't fetch already protected pages
    AND page_restrictions.pr_id IS NULL
    AND recentchanges.rc_timestamp > curdate() - interval 1 week
GROUP BY recentchanges.rc_title
HAVING MAX(recentchanges.rc_timestamp) > curdate() - interval 3 day AND COUNT(*) > 1
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query
All SQL code is licensed under CC0 License.

Checking query status...