Top 10 editors by number of edits in Odia WP

SQL

WITH
rev_filter AS (
  SELECT rev_actor, rev_page, COUNT(DISTINCT(rev_id)) as edit_count 
  FROM revision
  GROUP BY rev_actor
),
edits_filter AS(
  SELECT DISTINCT(actor_name) as username, edit_count, actor_user
  FROM rev_filter
  LEFT JOIN actor ON actor_id = rev_actor AND rev_actor IS NOT NULL
  LEFT JOIN page on page_id = rev_page
  WHERE page_namespace = 0
),
bot_filter AS(
  SELECT DISTINCT(username), edit_count, actor_user, user_editcount as rough_editcount
  FROM edits_filter
  LEFT JOIN user on actor_user = user_id
  LEFT JOIN user_groups on ug_user = user_id
  WHERE ug_group IS NULL OR (ug_group != 'bot')
)
SELECT username, edit_count, rough_editcount
FROM bot_filter
WHERE LOWER(username) NOT LIKE '%bot%' 
ORDER BY edit_count DESC
LIMIT 10
SQL

Checking query status...
