Last modified: 2014-01-03 15:56:11 UTC
This issue was converted from https://jira.toolserver.org/browse/DBQ-109. Summary: List of Portuguese Wikibooks users with 100+ edits, with number of edits and pages created (main namespace) and date of first edit Issue type: Task - A task that needs to be done. Priority: Minor Status: Done Assignee: Hoo man <hoo@online.de> ------------------------------------------------------------------------------- From: Helder <helder.wiki@gmail.com> Date: Fri, 22 Oct 2010 13:25:30 ------------------------------------------------------------------------------- I would like to have a list (or a wiki table if possible) with the following columns: * user name * number of (not deleted) edits in main namespace * date of first edit in main namespace * number of pages created in main namespace This seems to be similar to these other requests: * https://jira.toolserver.org/browse/DBQ-3 * https://jira.toolserver.org/browse/DBQ-26
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Mon, 27 Dec 2010 15:04:55 ------------------------------------------------------------------------------- SQL: SELECT user_data.user, user_data.edits, user_data.first_edit, creations.count as creation_count FROM (SELECT COUNT(*) as count, user FROM (SELECT rev_user_text as user FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_namespace = 0 AND page.page_is_redirect = 0 GROUP BY revision.rev_page) as tmp GROUP BY tmp.user) as creations INNER JOIN (SELECT revision.rev_user_text as user, COUNT(revision.rev_id) as edits, min(rev_timestamp) as first_edit FROM revision INNER JOIN page ON revision.rev_page = page.page_id WHERE page.page_namespace = 0 AND revision.rev_deleted = 0 GROUP BY revision.rev_user_text) as user_data ON user_data.user = creations.user WHERE edits > 100 GROUP by user_data.user; Result: http://toolserver.org/~hoo/dbq/dbq-109.txt (as list) http://toolserver.org/~hoo/dbq/dbq-109_wiki.txt (as wiki table)
------------------------------------------------------------------------------- From: Helder.wiki <helder.wiki@gmail.com> Date: Mon, 27 Dec 2010 19:43:36 ------------------------------------------------------------------------------- Thank you very much! Just for curiosity: how long a query like this takes to be accomplished? Would it be feasible to extend the list to "edits > 50" instead of "edits > 100"? If so, could you please generate such a list sorted by number of edits? (when you have the time)
------------------------------------------------------------------------------- From: Hoo man <hoo@online.de> Date: Tue, 28 Dec 2010 00:13:50 ------------------------------------------------------------------------------- On such a "small" database like the ptwikibooks one it runs in a few seconds (about 4 seconds on ptwikibooks). So here are the extended results: http://toolserver.org/~hoo/dbq/dbq-109_2.txt (plain text) http://toolserver.org/~hoo/dbq/dbq-109_2_wiki.txt (wiki markup)
------------------------------------------------------------------------------- From: Helder.wiki <helder.wiki@gmail.com> Date: Wed, 29 Dec 2010 23:25:18 ------------------------------------------------------------------------------- Thanks you again! I wish you a happy new year =)
This bug was imported as RESOLVED. The original assignee has therefore not been set, and the original reporters/responders have not been added as CC, to prevent bugspam. If you re-open this bug, please consider adding these people to the CC list: Original assignee: hoo@online.de CC list: hoo@online.de
