Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
Uncitoyen
.
Toggle Highlighting
SQL
-- Modify startdate and enddate to change dates SET @startdate = '20230101000000'; SET @enddate = '20231207235959'; -- regex for exempt users SET @exempt_username_pattern = ' \\süzgec'; -- Define for what is a sysop action SET @sysoplogtype = CAST( "( 'abusefilter', 'block', 'contentmodel', 'delete', 'import', 'managetags', 'massmessage', 'merge', 'protect', 'rights', 'tag', 'timedmediahandler' )" AS CHAR ); -- misc SET @logwhere = CONCAT( "ug_group = 'sysop' ", "AND log_type IN ",@sysoplogtype," ", "AND actor_name NOT REGEXP @exempt_username_pattern ", "AND log_timestamp BETWEEN @startdate AND @enddate " ); SET @zeroaction = CONCAT( "SELECT ug_user as userid, user_name as username, 0 ", "FROM user_groups JOIN user ON ug_user = user_id ", "WHERE ug_group = 'sysop' ", "AND user_name NOT REGEXP @exempt_username_pattern " ); -- show count of sysop actions in the term SET @logcountsql = CONCAT( "SELECT userid, username, SUM(sysoplogs) as SysopActionCount ", "FROM (", "SELECT ug_user as userid, actor_name as username, count(*) as sysoplogs FROM user_groups LEFT JOIN actor ON ug_user=actor_user JOIN logging ON actor_id = log_actor ", "WHERE ",@logwhere, "GROUP BY ug_user ", "UNION ", @zeroaction, ") as t GROUP BY userid ", "ORDER BY SysopActionCount asc " ); PREPARE stmt FROM @logcountsql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- show count of edits in the term SELECT userid, username, SUM(edit) as edits FROM ( SELECT ug_user as userid, actor_name as username, count(*) as edit FROM user_groups LEFT JOIN actor ON ug_user=actor_user JOIN revision ON actor_id = rev_actor WHERE ug_group = 'sysop' AND actor_name NOT REGEXP @exempt_username_pattern AND rev_timestamp BETWEEN @startdate AND @enddate GROUP BY ug_user UNION SELECT ug_user as userid, user_name as username, 0 as edit FROM user_groups JOIN user ON ug_user = user_id WHERE ug_group = 'sysop' AND user_name NOT REGEXP @exempt_username_pattern ) as t GROUP BY userid ORDER BY edits asc;
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
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...