Edits of sysops by NS

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

Total edits of sysops by namespace in Urdu Wikipedia

SQL

use urwiki_p;
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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' 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) = '201508' GROUP BY rev_user_text;
as edits_ns101 ON edits_ns101.user = rev_user_text
WHERE LEFT(rev_timestamp, 6) = '201508' 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;

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...