SQL
AخA
WITH wpedits (wp_actor, wp_name, wp_edits_in_wp) AS
(
SELECT actor_id, actor_name, COUNT(*)
FROM recentchanges
JOIN page ON page_namespace = 1 AND page_title = rc_title
JOIN categorylinks ON cl_from = page_id
JOIN actor_recentchanges ON actor_id = rc_actor
LEFT JOIN user_groups ON ug_user = actor_user AND ug_group = 'bot'
LEFT JOIN user ON user_id = actor_user
LEFT JOIN ipblocks_ipindex ON ipb_user = COALESCE(actor_user, 0) AND ipb_address = actor_name AND ipb_sitewide = 1
WHERE rc_namespace = 0
AND cl_to = 'All_WikiProject_Medicine_articles'
AND rc_type IN (0, 1) -- edit of existing page, new page
AND ug_group IS NULL
AND COALESCE(user_editcount, 0) < 10000
AND ipb_id IS NULL
GROUP BY rc_actor
HAVING COUNT(*) >= 10
)
SELECT wp_name AS `user`,
wp_edits_in_wp AS `project edits`,
COUNT(*) AS `total mainspace edits`,
100 * wp_edits_in_wp / COUNT(*) AS `% in project`
FROM wpedits
JOIN recentchanges_userindex ON rc_actor = wp_actor
WHERE rc_namespace = 0
AND rc_type IN (0, 1)
GROUP BY wp_actor
HAVING `% in project` >= 10
ORDER BY `project edits` DESC;
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.