Count by users the latest revisions that are a "revert with tools"

Fork of Latest revisions that are a "revert with tools" 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
  COUNT(*) as count,
  rev_actor,
  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)
GROUP BY rev_actor
ORDER BY count DESC
LIMIT 10;

SQL

Checking query status...