آمار کلی

This query is marked as a draft This query has been published by Elph.
SQL

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_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')
#AND creations_ns0.user  IN (SELECT pl_title FROM pagelinks WHERE pl_from = 3220948 )
GROUP BY creations_ns0.user ORDER BY article_creations DESC LIMIT 500;
;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
