SQL
x
# Unique human editors and their total edits for a list of articles specified by category, excluding few as may be
SET @pmonthbegin=DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH,'%Y-%m-01'),
@nmonthbegin=DATE_FORMAT(CURDATE() , '%Y-%m-01');
SELECT
subqres.user_name,
COUNT(DISTINCT subqres.page_title) as pages_edited,
SUM(subqres.Edits) as tot_edits
FROM (
SELECT
page_title,
user_name,
COUNT(*) as Edits
FROM page
JOIN categorylinks ON cl_from = page_id AND cl_to="Districts_of_Andhra_Pradesh" AND cl_type="page"
JOIN revision ON page_id = rev_page
JOIN actor ON rev_actor = actor_id
JOIN user ON user_id =actor_user
where rev_actor != 0 AND page_namespace=0 AND page_title !="List_of_districts_of_Andhra_Pradesh"
AND LEFT(rev_timestamp,8) >= REPLACE(@pmonthbegin,"-","")AND LEFT(rev_timestamp,8) < REPLACE(@nmonthbegin,"-","")
AND NOT (user_name LIKE '%AWB%' OR user_name LIKE '%Bot%' OR user_name LIKE'%bot%' OR user_name LIKE '%BOT%' OR user_name ='CommonsDelinker')
GROUP BY page_title,user_name) as subqres
GROUP BY subqres.user_name
ORDER BY tot_edits DESC, pages_edited DESC, user_name ASC
LIMIT 100;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.