SQL
x
SELECT creations_ns0.user,
first_edit.timestamp as first_edit,
creations_ns0.count as article_creations,
creations_ns10.count as template_creations,
creations_ns12.count as help_creations,
creations_ns14.count as category_creations,
creations_ns100.count as portal_creations,
creations_ns104.count as list_creations
FROM
(SELECT COUNT(*) as count,
creator.user as user
FROM
(SELECT revision.rev_user_text 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_user_text 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 = 10 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_ns10
ON creations_ns0.user = creations_ns10.user
LEFT JOIN
(SELECT COUNT(*) as count,
creator.user as user
FROM
(SELECT revision.rev_user_text 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 = 12 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_ns12
ON creations_ns0.user = creations_ns12.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM
(SELECT revision.rev_user_text 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 = 14 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_ns14
ON creations_ns0.user = creations_ns14.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM
(SELECT revision.rev_user_text 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 = 100 AND page.page_is_redirect = 0 AND page.page_title NOT IN (SELECT page_title FROM page WHERE page_title LIKE "%/%")
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_ns100 ON creations_ns0.user = creations_ns100.user
LEFT JOIN
(SELECT COUNT(*) as count, creator.user as user
FROM
(SELECT revision.rev_user_text 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 = 104 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_ns104 ON creations_ns0.user = creations_ns104.user
INNER JOIN
(SELECT revision.rev_user_text as user, min(rev_timestamp) as timestamp
FROM revision
GROUP BY rev_user_text) 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 50;
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.