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, user_id, user_registration, user_editcount, COUNT(rc_id) 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

    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_userindex ON rev_user = d.user_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;