rowiki users having over 625 edits + all bots, sorted by edit count

Fork of rowiki users with at least 900 edits + all bots, sorted by edit count by AndreyyshoreBot

This query is marked as a draft This query has been published by Imtiaz ahmed rifat.

This takes into account bots that don't have official bot status, but are nonetheless members of Categorie:Roboți Wikipedia. It also tells whether an account is an admin or a bot.

SQL

USE rowiki_p;
SELECT ss_total_edits FROM site_stats;
SELECT SUM(user_editcount) FROM user AS u
WHERE user_editcount <= 625;
SELECT *
FROM (
	SELECT fi.id AS userid, fi.name AS name, fi.edits AS editcount,
		CASE
			WHEN fi.admin=1 OR se.admin=1 THEN 1
			ELSE 0
		END AS admin,
		CASE
			WHEN (fi.bot=1 AND fi.bot IS NOT NULL) OR se.bot=1 THEN 1
			ELSE 0
		END AS bot
	FROM (
		SELECT user.user_id AS id, user.user_name AS name, user.user_editcount AS edits, ug.admin AS admin, ug.bot AS bot
		FROM user
		LEFT JOIN (
			SELECT ug_user AS id, SUM(admin) AS admin, SUM(bot) AS bot
			FROM (
				SELECT ug_user, ug_group='sysop' AS admin, ug_group='bot' AS bot FROM user_groups
			) AS ab1
			WHERE admin=1 OR bot=1
			GROUP BY ug_user
		) AS ug ON user.user_id=ug.id
		WHERE (user.user_editcount > 625 OR ug.bot=1)
	) AS fi
	LEFT JOIN (
		SELECT user.user_id AS id, 0 AS admin, 1 AS bot FROM user
		INNER JOIN (
			SELECT replace(page_title, '_', ' ') AS name
			FROM (
              	SELECT cl_from FROM categorylinks WHERE cl_to='Roboți_Wikipedia'
            ) AS catlinks
			INNER JOIN page ON catlinks.cl_from=page.page_id
			WHERE page_namespace=2
          	UNION
          	SELECT user_name AS name
          	FROM user
          	INNER JOIN (
              	SELECT REPLACE(pl_title, '_', ' ') AS us FROM pagelinks
              	WHERE pl_from = 1995229 AND pl_namespace = 2
            ) AS j ON user.user_name=j.us
		) AS au ON user.user_name=au.name
	) AS se ON fi.id=se.id
	WHERE fi.edits > 625 OR fi.bot = 1 OR se.bot = 1
	UNION
	SELECT fi2.id AS userid, fi2.name AS name, fi2.edits AS editcount,
		CASE
			WHEN fi2.admin=1 OR se2.admin=1 THEN 1
			ELSE 0
		END AS admin,
		CASE
			WHEN (fi2.bot=1 AND fi2.bot IS NOT NULL) OR se2.bot=1 THEN 1
			ELSE 0
		END AS bot
	FROM (
		SELECT user.user_id AS id, user.user_name AS name, user.user_editcount AS edits, ug2.admin AS admin, ug2.bot AS bot
		FROM user
		LEFT JOIN (
			SELECT ug_user AS id, SUM(admin) AS admin, SUM(bot) AS bot
			FROM (
				SELECT ug_user, ug_group='sysop' AS admin, ug_group='bot' AS bot FROM user_groups
			) AS ab2
			WHERE admin=1 OR bot=1
			GROUP BY ug_user
		) AS ug2 ON user.user_id=ug2.id
	) AS fi2
	RIGHT JOIN (
		SELECT user.user_id AS id, 0 AS admin, 1 AS bot FROM user
		INNER JOIN (
			SELECT replace(page_title, '_', ' ') AS name
			FROM (
              	SELECT cl_from FROM categorylinks WHERE cl_to='Roboți_Wikipedia'
            ) AS catlinks
			INNER JOIN page ON catlinks.cl_from=page.page_id
			WHERE page_namespace=2
          	UNION
          	SELECT user_name AS name
          	FROM user
          	INNER JOIN (
              	SELECT REPLACE(pl_title, '_', ' ') AS us FROM pagelinks
              	WHERE pl_from = 1995229 AND pl_namespace = 2
            ) AS j2 ON user.user_name=j2.us
		) AS au2 ON user.user_name=au2.name
	) AS se2 ON fi2.id=se2.id
) AS ro
WHERE editcount > 625 OR bot = 1
ORDER BY editcount DESC, userid
LIMIT 1500;

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