use enwiki_p;

SELECT user_name, user_editcount, user_registration, last_edit

FROM `user`

​

JOIN (select rev_user, max(rev_timestamp) as last_edit from revision_userindex group by rev_user) latest_edit_cnt on user_id = latest_edit_cnt.rev_user

JOIN (select rev_user, count(*) as mainspace_edits from revision_userindex join page on rev_page = page_id where page_namespace = 0 and rev_timestamp < 20171101000000 group by rev_user) mainspace_contrib_cnt on user_id = mainspace_contrib_cnt.rev_user

LEFT JOIN (select i.ipb_user as blocked_user, i.ipb_expiry as block_expires from ipblocks i join (select ipb_user, max(ipb_timestamp) as lb_ts from ipblocks group by ipb_user) lb on i.ipb_user = lb.ipb_user and i.ipb_timestamp = lb.lb_ts) cur_block on blocked_user = user_id

WHERE last_edit > 20161127000000

    AND user_registration < 20171028000000

    AND mainspace_edits > 149

    AND (block_expires is null or block_expires < 201711270000)

;