Untitled query #70607

SQL

SELECT user.user_name,
       counts.delete_count,
       counts.restore_count,
       counts.revision_count,
       counts.event_count,
       counts.protect_count,
       counts.unprotect_count,
       counts.modify_count,
       counts.block_count,
       counts.unblock_count,
       counts.reblock_count,
       counts.rights_count
FROM user
JOIN user_groups ON user.user_id = user_groups.ug_user
JOIN (
    SELECT actor_user,
           SUM(CASE WHEN log_type = 'delete' AND log_action NOT IN ('delete_redir','restore') THEN 1 ELSE 0 END) AS delete_count,
           SUM(CASE WHEN log_type = 'delete' AND log_action = 'restore' THEN 1 ELSE 0 END) AS restore_count,
           SUM(CASE WHEN log_type = 'delete' AND log_action = 'revision' THEN 1 ELSE 0 END) AS revision_count,
           SUM(CASE WHEN log_type = 'delete' AND log_action = 'event' THEN 1 ELSE 0 END) AS event_count,
           SUM(CASE WHEN log_type = 'protect' AND log_action IN ('modify','protect') THEN 1 ELSE 0 END) AS protect_count,
           SUM(CASE WHEN log_type = 'protect' AND log_action = 'unprotect' THEN 1 ELSE 0 END) AS unprotect_count,
           SUM(CASE WHEN log_type = 'protect' AND log_action = 'modify' THEN 1 ELSE 0 END) AS modify_count,
           SUM(CASE WHEN log_type = 'block' THEN 1 ELSE 0 END) AS block_count,
           SUM(CASE WHEN log_type = 'block' AND log_action = 'unblock' THEN 1 ELSE 0 END) AS unblock_count,
           SUM(CASE WHEN log_type = 'block' AND log_action = 'reblock' THEN 1 ELSE 0 END) AS reblock_count,
           SUM(CASE WHEN log_type = 'rights' THEN 1 ELSE 0 END) AS rights_count
    FROM logging
    JOIN actor ON logging.log_actor = actor.actor_id
    GROUP BY actor_user
) AS counts ON counts.actor_user = user.user_id
WHERE user_groups.ug_group = 'sysop'
ORDER BY user.user_name ASC, counts.delete_count DESC, counts.restore_count DESC, counts.revision_count DESC, counts.event_count DESC, counts.protect_count DESC, counts.unprotect_count DESC, counts.modify_count DESC, counts.block_count DESC, counts.unblock_count DESC, counts.reblock_count DESC, counts.rights_count DESC
SQL

Checking query status...
