Untitled query #77566

SQL

#Preliminary approach: return user edit counts, a field already tracked by wikis. 
#caveats: not accurate, for details refer to Manual:user table, user_editcount
#SELECT user.user_id, user.user_name, user.user_editcount FROM user
#WHERE NOT EXISTS (   -- the following subquery selects users that are bots and NOT EXISTS filters them out
#  SELECT 1 FROM user_groups
#  WHERE user.user_id = user_groups.ug_user
#  AND user_groups.ug_group = 'bot'
#)
#ORDER BY user_editcount DESC
#LIMIT 10

#Approach 2
#Inner join / Extracting all revisions made by all actors that have made a revision in this wiki
#Tables used: actor, revision, user_groups
SELECT actor.actor_name AS Editor, COUNT(revision.rev_id) AS Num_Edits 
-- COUNT is used to aggreate number of revisions made by a particular actor
FROM revision
INNER JOIN actor ON actor.actor_id = revision.rev_actor
-- optional criteria: filtering article edits, bots, minor edits ---------
#INNER JOIN page ON revision.rev_page = page.page_id -- get only pages/articles that have been edited. for some users, 5-10% edits are non-article edits
WHERE NOT EXISTS (   -- the following subquery selects users that are bots, and NOT EXISTS filters them out
  SELECT 1 FROM user_groups
  WHERE actor.actor_user = user_groups.ug_user
  AND user_groups.ug_group = 'bot'
)
#AND revision.rev_minor_edit = 0 -- only include major edits as minor edits can often be typo correction/automated mass edits
#INNER JOIN user_groups ON actor.actor_user = user_groups.ug_user -- join with user_group table using id to find if user is a bot
#WHERE user_groups.ug_group != 'bot' -- exclude bots from selection
-- can remove comment delimiters if bots are to be filtered ----------------------------------
GROUP BY Editor -- grouping the aggregate count by editor who performed the revisions
ORDER BY Num_Edits DESC
LIMIT 10 -- getting top 10 from descending ordered data

#Interestingly, the two approaches above result in slightly different rankings
#Some user edit counts are undercounted and some are overcounted in the first approach
#Since it doesn't involve a join, first approach is faster
#both approaches are slow for large wikis/more users
SQL

Checking query status...
