Latest revisions that are a "revert with tools"

Fork of What are number of tagged revisions relevant to vandalisms by Xinbenlv

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

SQL

# schema: https://www.mediawiki.org/w/index.php?title=Manual:Database_layout/diagram&action=render

# SELECT * FROM change_tag_def LIMIT 1000;

# revert with tools
# 1, 8, 32, mw-undo, mw-rollback, rapid reverts
# 7, 63: huggle, stiki, AWB

SELECT 
  rev_timestamp,
  CONCAT("Special:Diff/",rev_id) as url, 
  rev_id as rev, 
  ctd_name as tag, 
  page_namespace as pns, 
  page_title as page, 
  actor_name as user 
FROM change_tag
INNER JOIN revision ON ct_rev_id=rev_id
INNER JOIN logging ON ct_log_id=log_id
LEFT JOIN page on rev_page = page_id
LEFT JOIN actor on rev_actor = actor_id
LEFT JOIN change_tag_def ON ct_tag_id = ctd_id
WHERE 
  ct_tag_id IN (1,7,8,32,63)
ORDER BY ct_id DESC
LIMIT 10;

SQL

Checking query status...