This query is marked as a draft This query has been published by Cryptic.

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.

Checking query status...