SQL
x
use arwiki_p;
SELECT
creations_ns0.user,
first_edit.timestamp as first_edit,
creations_ns0.count as article_creations,
creations_ns10.count as template_creations,
creations_ns999.count as redirect_creations
FROM (SELECT COUNT(*) as count,
creator.user as user
FROM (SELECT revision.rev_actor as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit
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 first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns0
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM (SELECT revision.rev_actor as user
FROM (SELECT rev_page, MIN(rev_timestamp) as first_edit FROM page INNER JOIN revision ON page.page_id = revision.rev_page WHERE page.page_is_redirect = 1 GROUP BY revision.rev_page)
as first_edit INNER JOIN revision ON first_edit.rev_page = revision.rev_page AND first_edit.first_edit = rev_timestamp) as creator GROUP BY creator.user) as creations_ns999 ON creations_ns0.user = creations_ns999.user
INNER JOIN (SELECT revision.rev_actor as user, min(rev_timestamp) as timestamp FROM revision GROUP BY rev_actor) as first_edit ON creations_ns0.user = first_edit.user
WHERE creations_ns0.user NOT IN
(SELECT user_name FROM user_groups
INNER JOIN user ON user_id = ug_user
WHERE ug_group = 'bot')
GROUP BY creations_ns0.user ORDER BY article_creations DESC LIMIT 20;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.