Fork of AP districts improvement in enwiki - current month by Arjunaraoc
This query is marked as a draft This query has been published by Arjunaraoc.

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.

Checking query status...