Untitled query #50802

SQL

SELECT 
    u.user_name, 
    u.user_editcount, 
    u.user_registration, 
    rev.rev_timestamp AS 'Last edit', 
    GROUP_CONCAT(DISTINCT ug1.ug_group) AS 'user_groups'
FROM 
    `user` u
JOIN 
    actor a ON a.actor_user = u.user_id
JOIN 
    revision_userindex rev_ui ON rev_ui.rev_id = (
        SELECT rev_id 
        FROM revision_userindex 
        WHERE rev_ui.actor_id = rev_ui.rev_actor
        ORDER BY rev_ui.rev_timestamp DESC 
        LIMIT 1
    )
JOIN 
    revision rev ON rev.rev_id = rev_ui.rev_id
LEFT JOIN 
    user_groups ug1 ON u.user_id = ug1.ug_user
WHERE 
    rev.rev_timestamp < DATE_FORMAT(NOW() - INTERVAL 1825 DAY, '%Y%m%d%H%i%S')
GROUP BY 
    u.user_id, u.user_name, u.user_editcount, u.user_registration, rev.rev_timestamp
HAVING 
    SUM(CASE WHEN ug1.ug_group = 'image-reviewer' THEN 1 ELSE 0 END) > 0
    AND SUM(CASE WHEN ug1.ug_group = 'bot' THEN 1 ELSE 0 END) > 0
ORDER BY 
    rev.rev_timestamp;
SQL

Checking query status...
