USE enwiki_p;

SELECT 

    actor_name as `Username`,

    user_editcount as `Edit count`,

    user_registration as `Registration date`, 

    NOT(ug_group IS NULL)  as `30/500`, 

    COUNT(*) as `RedWarn edits`, 

    COUNT(*)/user_editcount*100 as `RedWarn %`, 

    ipb_sitewide as `Currently blocked`

FROM revision_userindex

JOIN change_tag ON ct_rev_id = rev_id

JOIN actor_revision ON rev_actor = actor_id

JOIN `user` ON actor_user = user_id

LEFT JOIN user_groups ON ug_user = user_id AND ug_group = "extendedconfirmed"

LEFT JOIN ipblocks ON user_id = ipb_user

WHERE ct_tag_id = 577 -- RedWarn

GROUP BY actor_name

ORDER BY user_registration DESC