RedWarn users without rollback

Fork of RedWarn users without rollback by ProcrastinatingReader

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

SQL

USE enwiki_p;
SELECT actor_name, user_editcount, user_registration, (user_editcount > 500 AND user_registration < 20200627000000) as `30/500`, COUNT(*) as `RedWarn edits`, COUNT(*)/user_editcount*100 as `RedWarn %`, ipb_sitewide
FROM revision_userindex
JOIN change_tag ON ct_rev_id = rev_id
JOIN actor_revision ON rev_actor = actor_id
JOIN `user` ON actor_user = user_id
LEFT JOIN ipblocks ON user_id = ipb_user
WHERE ct_tag_id = 577 -- RedWarn
AND NOT EXISTS (
  SELECT 1 FROM user_groups WHERE ug_user = user_id AND ug_group = 'rollbacker'
)
GROUP BY actor_name
ORDER BY COUNT(*) DESC

SQL

Checking query status...