Untitled query #77532

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 aggregate number of revisions made by a particular actor
FROM actor
INNER JOIN revision ON actor.actor_id = revision.rev_actor
-- optional criteria: filtering namespaces, bots, minor edits -----------------------------------
INNER JOIN page ON revision.rev_page = page.page_id -- link pages table to retrieve namespaces. for some users, upto 25-30% are non main article edits
WHERE page.page_namespace IN (0, 6) -- 0 - Main article, 6 - File namespace / to add other namespaces: https://www.mediawiki.org/wiki/Manual:Namespace#Built-in_namespaces
AND NOT EXISTS (   -- the following subquery selects users that have 'bot' group affiliation, 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
-- can add/remove comment delimiters on above lines to toggle filters ---------------------------
GROUP BY Editor -- grouping the aggregate count by editor who performed the revisions
ORDER BY Num_Edits DESC -- sorting count in descending order
LIMIT 10 -- getting top 10 from sorted order

#Interestingly, the two approaches above result in slightly different rankings
#User edit counts are undercounted, overcounted in the first approach. some are also the same as counts in 2nd approach.
#Since edit counts are directly queried without a join, first approach is faster
#both approaches are slow for large wikis/more users
SQL

Checking query status...
