Users with most edits on the mainspace in the past month (bots and IPs filtered)

Fork of Users with most edits in the past month with bots and IPs filtered by DHN

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

SQL

WITH groups AS (
  SELECT ug_user, GROUP_CONCAT(ug_group) AS memberships 
  FROM user_groups 
  GROUP BY ug_user)
SELECT
  CONCAT('Kullanıcı mesaj:',actor.actor_name) AS Kullanıcı,
  memberships AS KullanıcıGrubu,
  COUNT(rev_id) AS DegisiklikSayisi_main
FROM revision LEFT JOIN actor ON (revision.rev_actor = actor.actor_id)
  LEFT JOIN groups ON (actor.actor_user = groups.ug_user)
  JOIN page ON (revision.rev_page = page.page_id)
-- Date
WHERE DATEDIFF(CURDATE(), rev_timestamp) < 60
-- IPs out
  AND actor.actor_user IS NOT NULL
-- Bots out
  AND (groups.memberships IS NULL OR LOCATE('bot', memberships) = 0)
-- Mainspace
  AND (page.page_namespace=4)
-- Group by user.
GROUP BY Kullanıcı
-- 
ORDER BY DegisiklikSayisi_main DESC
LIMIT 200;

SQL

Checking query status...