Untitled query #70607

This query is marked as a draft This query has been published by لوقا.
SQL

#Executed in 160.75 seconds
/*
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


*/

SELECT user_name,

           (select count(*) from logging
                    inner join actor on actor_id = log_actor
                    where actor_user = user_id and log_type = "delete"  and log_action not in ("delete_redir","restore") ) as "delete_count",
           (select count(*) from logging
                    inner join actor on actor_id = log_actor
                    where actor_user = user_id and log_type = "delete" and log_action = "restore" ) as "restore_count",

           (select count(*) from logging
                    inner join actor on actor_id = log_actor
                    where actor_user = user_id and log_type = "delete" and log_action = "revision" ) as "revision_count",
           (select count(*) from logging
                    inner join actor on actor_id = log_actor
                    where actor_user = user_id and log_type = "delete" and log_action = "event"  ) as "event_count",
           (select count(*) from logging
                    inner join actor on actor_id = log_actor
                    where actor_user = user_id and log_type = "protect" and log_action in("modify","protect")) as "protect_count",
       (select count(*) from logging
                                 inner join actor on actor_id = log_actor
        where actor_user = user_id and log_type = "protect" and log_action = "unprotect") as "unprotect_count",
       (select count(*) from logging
                                 inner join actor on actor_id = log_actor
        where actor_user = user_id and log_type = "protect" and log_action = "modify") as "modify_count",
       (select count(*) from logging
                                 inner join actor on actor_id = log_actor
        where actor_user = user_id and log_type = "block" ) as "block_count",
       (select count(*) from logging
                                 inner join actor on actor_id = log_actor
        where actor_user = user_id and log_type = "block" and log_action = "unblock") as "unblock_count",
       (select count(*) from logging
                                 inner join actor on actor_id = log_actor
        where actor_user = user_id and log_type = "block" and log_action = "reblock") as "reblock_count",
       (select count(*) from logging
                                 inner join actor on actor_id = log_actor
        where actor_user = user_id and log_type = "rights" ) as "rights_count"

FROM user
         JOIN user_groups ON user_id = ug_user
WHERE ug_group = "sysop"
ORDER BY user_name ASC , delete_count DESC, restore_count DESC, revision_count DESC, event_count DESC, protect_count DESC, unprotect_count DESC, modify_count DESC, block_count DESC, unblock_count DESC, reblock_count DESC, rights_count DESC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
