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, d.user_editcount, ipb_expiry
FROM (SELECT user_name, actor_id, user_id, user_registration, user_editcount, COUNT(rc_id) AS main_space_1m
	FROM user
        JOIN actor ON user_id = actor_id
		JOIN recentchanges_userindex ON rc_actor = actor_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
    AND rc_type <= 1
	GROUP BY user_id
	HAVING COUNT(*) >= 5
 ) AS d /* contains users with 5 main space edits in the last month (=recentchanges) */
	JOIN revision ON rev_actor = d.actor_id
    JOIN page ON rev_page = page_id
	LEFT JOIN ipblocks ON ipb_user = d.user_id
WHERE page_namespace = 0
GROUP BY d.user_id
HAVING main_space_total >= 50
ORDER BY d.user_registration DESC;

SQL

Checking query status...