Untitled query #16901

This query is marked as a draft This query has been published by جواد.
SQL

SELECT  * FROM (
(SELECT rev_user_text as user, 'sysop' as user_group, COUNT(*) as edits_all, edits_ns0, edits_ns1, edits_ns2, edits_ns3, edits_ns4, edits_ns5, edits_ns6, edits_ns7, edits_ns8, edits_ns9, edits_ns10, edits_ns11, edits_ns12, edits_ns13, edits_ns14, edits_ns15, edits_ns100, edits_ns101, all_time.edits as edits_total
FROM revision
INNER JOIN
(SELECT COUNT(*) as edits, rev_user_text as user FROM revision WHERE rev_user != 0 GROUP BY rev_user_text)
as all_time ON all_time.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns0, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 0 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns0 ON edits_ns0.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns1, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 1 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns1 ON edits_ns1.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns2, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 2 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns2 ON edits_ns2.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns3, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 3 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns3 ON edits_ns3.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns4, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 4 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns4 ON edits_ns4.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns5, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 5 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns5 ON edits_ns5.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns6, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 6 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns6 ON edits_ns6.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns7, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 7 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns7 ON edits_ns7.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns8, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 8 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns8 ON edits_ns8.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns9, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 9 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns9 ON edits_ns9.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns10, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 10 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns10 ON edits_ns10.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns11, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 11 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns11 ON edits_ns11.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns12, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 12 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns12 ON edits_ns12.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns13, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 13 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns13 ON edits_ns13.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns14, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 14 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns14 ON edits_ns14.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns15, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 15 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns15 ON edits_ns15.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns100, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 100 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns100 ON edits_ns100.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns101, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 101 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns101 ON edits_ns101.user = rev_user_text
WHERE LEFT(rev_timestamp, 6) = '201108' AND rev_user != 0 AND rev_user IN (SELECT ug_user FROM user_groups WHERE ug_group = 'sysop') AND rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot')
GROUP BY rev_user_text
ORDER BY edits_all DESC LIMIT 100)


/* normal users */
UNION (SELECT rev_user_text as user, 'user' as user_group, COUNT(*) as edits_all, edits_ns0, edits_ns1, edits_ns2, edits_ns3, edits_ns4, edits_ns5, edits_ns6, edits_ns7, edits_ns8, edits_ns9, edits_ns10, edits_ns11, edits_ns12, edits_ns13, edits_ns14, edits_ns15, edits_ns100, edits_ns101, all_time.edits as edits_total
FROM revision
INNER JOIN
(SELECT COUNT(*) as edits, rev_user_text as user FROM revision WHERE rev_user != 0 GROUP BY rev_user_text)
as all_time ON all_time.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns0, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 0 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns0 ON edits_ns0.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns1, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 1 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns1 ON edits_ns1.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns2, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 2 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns2 ON edits_ns2.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns3, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 3 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns3 ON edits_ns3.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns4, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 4 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns4 ON edits_ns4.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns5, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 5 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns5 ON edits_ns5.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns6, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 6 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns6 ON edits_ns6.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns7, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 7 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns7 ON edits_ns7.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns8, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 8 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns8 ON edits_ns8.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns9, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 9 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns9 ON edits_ns9.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns10, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 10 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns10 ON edits_ns10.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns11, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 11 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns11 ON edits_ns11.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns12, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 12 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns12 ON edits_ns12.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns13, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 13 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns13 ON edits_ns13.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns14, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 14 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns14 ON edits_ns14.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns15, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 15 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns15 ON edits_ns15.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns100, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 100 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns100 ON edits_ns100.user = rev_user_text
LEFT JOIN
(SELECT COUNT(*) as edits_ns101, rev_user_text as user FROM revision INNER JOIN page ON rev_page = page_id WHERE rev_user != 0 AND page_namespace = 101 AND LEFT(rev_timestamp, 6) = '201108' GROUP BY rev_user_text)
as edits_ns101 ON edits_ns101.user = rev_user_text
WHERE LEFT(rev_timestamp, 6) = '201108' AND rev_user != 0 AND rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'bot') AND rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group = 'sysop')
GROUP BY rev_user_text
ORDER BY edits_all DESC LIMIT 100)) as data ORDER BY data.edits_all DESC LIMIT 100
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...
