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
Sunfyre
.
Toggle Highlighting
SQL
use fawiki_p; select concat('[[User:', user_name, '|]]'), concat(left(log_timestamp, 4), '-', mid(log_timestamp, 5, 2), '-', mid(log_timestamp, 7, 2)) as acct_created, concat(left(first_timestamp, 4), '-', mid(first_timestamp, 5, 2), '-', mid(first_timestamp, 7, 2)) as first_edit, concat('{{formatnum:', onefifty.cnt, '}}') as article_edits, concat('{{formatnum:', onehundred.cnt, '}}') as recent_edits, '' as voter_status from user left join logging on log_user = user_id and log_type = 'newusers' and log_action in ('create', 'autocreate') -- creating main account and log_timestamp < '20180720000000' -- occured 3 or more months prior to nomination start date join ( select rev_user, min(rev_timestamp) as first_timestamp from revision group by rev_user ) first_edit on user_id = first_edit.rev_user join ( select rev_user, count(*) cnt from revision join page on page_id = rev_page where page_namespace = 0 and rev_timestamp < '20181020000000' -- edits prior to nomination start date group by rev_user having count(*) >= 500 ) onefifty -- at least 500 edits in main namspace on user_id = onefifty.rev_user join ( select rev_user, count(*) cnt from revision join page on page_id = rev_page where page_namespace = 0 and rev_timestamp > '20180420000000' -- last 6 months leading to nomination start date and rev_timestamp < '20181020000000' group by rev_user having count(*) >= 100 ) onehundred -- at least 100 edits in the main namespace in the last six months leading to the nomination start date on user_id = onehundred.rev_user left join ( select ug_user from user_groups where ug_group = 'bot' ) bots on bots.ug_user = user_id where bots.ug_user is null and ( log_timestamp is not null or first_timestamp < '20180720000000' -- occured 3 or more months prior to nomination start date ) order by user_name
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...