/* Moves by non-autoconfirmed (Not worth querying, debatable whether it is actually a violation)

use enwiki_p;

select log_id, log_type, log_action, user_name, user_editcount, user_registration from logging

join actor on actor_id = log_actor join user on user_id=actor_user where log_type="move" 

and (user_editcount < 10 || user_registration > (DATE_FORMAT((NOW() - INTERVAL 4 DAY),'%Y%m%d%H%i%S'))) 

and not exists (select 1 from user_groups where ug_user=user_id and ug_group="confirmed")

and log_timestamp > "20190501010101" limit 100;

*/ 

/* Moves without redirect */

use enwiki_p;

set @by_date = "20200801010101";

select log_id, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging

join actor_logging on actor_id = log_actor join user on user_id = actor_user join comment_logging on comment_id = log_comment_id 

where log_type="move" and log_params like '%"5::noredir";s:1:"1%' and log_timestamp > @by_date  

and comment_text not like "Automatically moved page while renaming the user%" and log_namespace != 828

-and not exists (select 1 from user_groups where ug_user = actor_user and (ug_group="extendedmover" || ug_group="sysop" || ug_group="bot"));

/* Other common admin actions */

select log_id, log_type, log_action, user_name, user_editcount, user_registration, log_params from logging

join actor_logging on actor_id = log_actor join user on user_id = actor_user

where (log_type = "block" || (log_type = "protect" and log_action != "move_prot") || (log_type="delete" && log_action != "delete_redir")

      || log_type="contentmodel" /* Blame Sophivorus for this */)

and not exists (select 1 from user_groups where ug_user = actor_user and ug_group="sysop") and log_timestamp > @by_date;

/* Edits to protected pages */

/* Semi-protection not worth querying and of debatable applicability */

/* Extended-confirmed protection */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page_restrictions on pr_page=rev_page join page on page_id=rev_page 

join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where pr_type="edit" and pr_level="extendedconfirmed" and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="extendedconfirmed" || ug_group="sysop" || ug_group="bot"))

and not exists (select 1 from logging_logindex where log_namespace=page_namespace 

                and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);

/* Template protection */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page_restrictions on pr_page=rev_page join page on page_id=rev_page 

join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where pr_type="edit" and pr_level="templateeditor" and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and (ug_group="templateeditor" || ug_group="sysop"))

and not exists (select 1 from logging_logindex where log_namespace=page_namespace 

                and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);

/* Full protection */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page_restrictions on pr_page=rev_page join page on page_id=rev_page 

join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where pr_type="edit" and pr_level="sysop" and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop")

and not exists (select 1 from logging_logindex where log_namespace=page_namespace 

                and log_title=page_title and log_type="protect" and log_timestamp > rev_timestamp);

/* MediaWiki namespace */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page on page_id=rev_page join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where page_namespace=8 and rev_timestamp > @by_date

and not exists (select 1 from user_groups where ug_user=user_id and ug_group="sysop");

/* Edits to site CSS/JS */

select user_name, user_editcount, user_registration, rev_id, page_namespace, page_title from revision 

join page on page_id=rev_page join actor_revision on rev_actor=actor_id join user on user_id=actor_user

where page_namespace=8 and (page_content_model="css" || page_content_model="javascript")

and rev_timestamp > @by_date and not exists (select 1 from user_groups where ug_user=user_id and ug_group="interface-admin");

/* Rate-limit */

SELECT (SELECT COUNT(*) from logging_userindex l2 

    WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+86400) and l2.log_actor=l1.log_actor

        and l2.log_type="newusers") as creation_count, 

actor_user, actor_name, l1.log_timestamp from logging l1 join actor_logging on log_actor=actor_id 

where l1.log_timestamp > @by_date and l1.log_type="newusers" 

and not exists (select 1 from user_groups where ug_user=actor_user and (ug_group="sysop" || ug_group="accountcreator" || ug_group="eventcoordinator"))

and (SELECT COUNT(*) from logging_userindex l2 

    WHERE l2.log_timestamp >= l1.log_timestamp and l2.log_timestamp < (l1.log_timestamp+86400) and l2.log_actor=l1.log_actor

        and l2.log_type="newusers") > 6;

/* XXX there are a few more ratelimits, but some are unlogged, and I don't consider it within scope of this project to 

call out users for doing something as trivial as editing or moving slightly too fast, so only focusing on the 

4 accounts/day limit" */