Untitled query #40705

SQL

SELECT 
creations_ns0.user, 
first_edit.timestamp as first_edit, 
creations_ns0.count as article_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;
SQL

Checking query status...
