Edits & Editors Query #3

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

Monthly and Annual edit and editors counts from  July 1, 2013 through June 30, 2015 for pages Torget and Tinget

SQL

# DATA COUNTS FROM July 1, 2013 through June 30,2015
# GREY TEXT IS DESCRIPTIVE ONLY

#-----PAGE EDITORS

#unique editors to the Tinget pump page annually
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
COUNT(distinct rev_user) 
#SPECIFY THE MEASURE:
AS editors 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Tinget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`
ORDER BY `year` ASC;



#unique editors to the Torget pump page annually
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
COUNT(distinct rev_user) 
#SPECIFY THE MEASURE:
AS editors 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Torget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`
ORDER BY `year` ASC;

#unique editors to the Tinget pump page monthly
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) 
#SPECIFY THE MEASURE:
AS editors 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Tinget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`, `month` 
ORDER BY `year` ASC, `month` ASC;

#unique editors to the Torget pump page monthly
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)
#SPECIFY THE MEASURE:
AS editors 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Torget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`, `month` 
ORDER BY `year` ASC, `month` ASC;

#Edits annually to Tinget pump page
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
COUNT(rev_id) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Tinget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`
ORDER BY `year` ASC;

#Edits annually to Torget pump page
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
COUNT(rev_id) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Torget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`
ORDER BY `year` ASC;

#Edits monthly to Tinget pump page
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) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Tinget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`, `month` 
ORDER BY `year` ASC, `month` ASC;

#Edits monthly to Torget pump page
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) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Torget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`, `month` 
ORDER BY `year` ASC, `month` ASC;

#Edits annually by WMNOjorid and WMNOastrid on Tinget pump page
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
COUNT(rev_id) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Tinget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#TO RESTRICT TO CERTAIN USERS WE ADD THE LINE:
AND r.rev_user_text IN ("WMNOjorid","WMNOastrid") 
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`
ORDER BY `year` ASC;

#Edits annually by WMNOjorid and WMNOastrid on Torget pump page
SELECT EXTRACT(Year from DATE_FORMAT(rev_timestamp,'%Y%m%d%H%i%s')) as `year`, 
COUNT(rev_id) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Torget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#TO RESTRICT TO CERTAIN USERS WE ADD THE LINE:
AND r.rev_user_text IN ("WMNOjorid","WMNOastrid") 
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`
ORDER BY `year` ASC;

#Edits monthly by WMNOjorid and WMNOastrid on Tinget pump page
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) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Tinget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#TO RESTRICT TO CERTAIN USERS WE ADD THE LINE:
AND r.rev_user_text IN ("WMNOjorid","WMNOastrid") 
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`, `month` 
ORDER BY `year` ASC, `month` ASC;

#Edits monthly by WMNOjorid and WMNOastrid on Torget pump page
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) 
#SPECIFY THE MEASURE:
AS edits 
#SPECIFY THE PROJECT:
FROM nowiki_p.revision r
#AGAIN SPECIFY THE PROJECT:
INNER JOIN nowiki_p.page AS p
ON r.rev_page = p.page_id 
WHERE p.page_title 
#SPECIFY THE PAGE:
IN ("Torget") 
#SPECIFY THE NAMESPACE:
AND p.page_namespace=4
#TO RESTRICT TO CERTAIN USERS WE ADD THE LINE:
AND r.rev_user_text IN ("WMNOjorid","WMNOastrid") 
#SPECIFY THE TIME PERIOD START
AND r.rev_timestamp > 20130101000000 
#SPECIFY THE TIME PERIOD END
AND r.rev_timestamp < 20150630000000
AND r.rev_user != 0 
AND r.rev_user NOT IN (SELECT ug_user FROM nowiki_p.user_groups WHERE ug_group = 'bot') #not a registered bot
GROUP BY `year`, `month` 
ORDER BY `year` ASC, `month` 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.

SQL

Checking query status...