Wikidata: 2021 top patrollers (main namespace)

SQL

WITH groups AS (
  SELECT ug_user, GROUP_CONCAT(ug_group) AS memberships 
  FROM user_groups 
  GROUP BY ug_user)
SELECT
  actor.actor_name AS anv,
  memberships AS grupper,
  COUNT(rev_id) AS redigeringar
FROM revision LEFT JOIN actor ON (revision.rev_actor = actor.actor_id)
  LEFT JOIN groups ON (actor.actor_user = groups.ug_user)
-- Lọc theo thời gian
WHERE DATEDIFF("2021-01-01", rev_timestamp) > 0 AND DATEDIFF("2021-01-01", rev_timestamp) < 365

-- Lọc ra IP
  AND actor.actor_user IS NOT NULL
-- Lọc ra bot
  AND (groups.memberships IS NULL OR LOCATE('bot', memberships) = 0)
-- Group by user.
GROUP BY anv
-- Giới hạn 200 người có số sửa đổi cao nhất
ORDER BY redigeringar DESC
LIMIT 200;
SQL

Checking query status...
