Grants namespace edits and editors (non-WMF)

This query is marked as a draft This query has been published by EGalvez (WMF).
SQL

#edits to the grants namespace by non-WMF
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
EXTRACT(Month from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `month`, 
COUNT(rev_id) AS edits FROM metawiki_p.revision r
INNER JOIN metawiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_namespace IN (200, 201) #Grants and Grants_talk namespaces
AND r.rev_user != 0 #exclude IP edits and editorsAND r.rev_timestamp > 20141001000000
AND r.rev_timestamp > 20140701000000
AND r.rev_timestamp < 20141001000000
AND r.rev_user NOT IN (SELECT ug_user FROM metawiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
AND r.rev_user_text NOT LIKE "%(WMF)" #not a standard staff account
AND r.rev_user_text NOT IN ("Ijon", "Frank_Schulenburg", "Jtmorgan","SarahStierch","Wolliff","anasuyas","Chedasaurus","Ciphers","floorkoudijs","Janstee","jwild","kittycarmichael","Macruzbar","Msannakoval","Opinenow","Satihsu","Tighef","zhengan"); 


#unique non-WMF editors to the grants namespace
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
EXTRACT(Month from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `month`, 
COUNT(distinct rev_user) AS editors FROM metawiki_p.revision r
INNER JOIN metawiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_namespace IN (200, 201) #Grants and Grants_talk namespaces
AND r.rev_user != 0 #exclude IP edits and editorsAND r.rev_timestamp > 20141001000000
AND r.rev_timestamp > 20140701000000
AND r.rev_timestamp < 20141001000000
AND r.rev_user NOT IN (SELECT ug_user FROM metawiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
AND r.rev_user_text NOT LIKE "%(WMF)" #not a standard staff account
AND r.rev_user_text NOT IN ("Ijon", "Frank_Schulenburg", "Jtmorgan","SarahStierch","Wolliff","anasuyas","Chedasaurus","Ciphers","floorkoudijs","Janstee","jwild","kittycarmichael","Macruzbar","Msannakoval","Opinenow","Satihsu","Tighef","zhengan");


#edits to the Grants namespacy by WMF editors
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
EXTRACT(Month from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `month`, 
COUNT(rev_id) AS edits FROM metawiki_p.revision r
INNER JOIN metawiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_namespace IN (200, 201) #Grants and Grants_talk namespaces
AND r.rev_user != 0 #exclude IP edits and editorsAND r.rev_timestamp > 20141001000000
AND r.rev_timestamp > 20140701000000
AND r.rev_timestamp < 20141001000000
AND r.rev_user NOT IN (SELECT ug_user FROM metawiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
AND (r.rev_user_text LIKE "%(WMF)" #not a standard staff account
OR r.rev_user_text IN ("Ijon", "Frank_Schulenburg", "Jtmorgan","SarahStierch","Wolliff","anasuyas","Chedasaurus","Ciphers","floorkoudijs","Janstee","jwild","kittycarmichael","Macruzbar","Msannakoval","Opinenow","Satihsu","Tighef","zhengan"));

#unique WMF editors in the Grants namespace
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
EXTRACT(Month from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `month`, 
COUNT(distinct rev_user) AS editors FROM metawiki_p.revision r
INNER JOIN metawiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_namespace IN (200, 201) #Grants and Grants_talk namespaces
AND r.rev_user != 0 #exclude IP edits and editorsAND r.rev_timestamp > 20141001000000
AND r.rev_timestamp > 20140701000000
AND r.rev_timestamp < 20141001000000
AND r.rev_user NOT IN (SELECT ug_user FROM metawiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
AND (r.rev_user_text LIKE "%(WMF)" #not a standard staff account
OR r.rev_user_text IN ("Ijon", "Frank_Schulenburg", "Jtmorgan","SarahStierch","Wolliff","anasuyas","Chedasaurus","Ciphers","floorkoudijs","Janstee","jwild","kittycarmichael","Macruzbar","Msannakoval","Opinenow","Satihsu","Tighef","zhengan"));
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.
SQL

Checking query status...
