1ヶ月以上前に登録し、全名前空間を通じて50回以上編集しており、直近1ヶ月間に標準名前空間を5回以上編集しているアカウント

SQL

USE jawiki_p;
SELECT d.user_name, d.user_registration, d.main_space_1m, COUNT(rev_id) AS main_space_total, ipb_expiry
FROM (SELECT user_name, user_id, user_registration, user_editcount, COUNT(*) AS main_space_1m
	FROM user
		JOIN recentchanges_userindex ON user_id = rc_user
	WHERE user_editcount >= 50
	AND (user_registration IS NULL OR user_registration < REGEXP_REPLACE(DATE_SUB(NOW(), INTERVAL 1 month), "[ :\-]", ""))
	AND rc_timestamp > REGEXP_REPLACE(DATE_SUB(NOW(), INTERVAL 1 month), "[ :\-]", "")
	AND rc_namespace = 0
	GROUP BY user_id
	HAVING COUNT(*) >= 5
 ) AS d
	JOIN revision_userindex ON rev_user = d.user_id
    LEFT JOIN page ON rev_page = page_id
	LEFT JOIN ipblocks ON ipb_user = d.user_id
WHERE rev_namespace = 0
GROUP BY d.user_id
HAVING main_space_total >= 50
ORDER BY user_editcount DESC;

SQL

Checking query status...