activity_of_bureaucrats

SQL

SELECT 
  u.user_name, 
  SUM(CASE WHEN l.log_type = 'delete' AND l.log_action NOT IN ('delete_redir', 'restore') THEN 1 ELSE 0 END) AS delete_count,
  SUM(CASE WHEN l.log_type = 'delete' AND l.log_action = 'restore' THEN 1 ELSE 0 END) AS restore_count,
  SUM(CASE WHEN l.log_type = 'delete' AND l.log_action = 'revision' THEN 1 ELSE 0 END) AS revision_count,
  SUM(CASE WHEN l.log_type = 'delete' AND l.log_action = 'event' THEN 1 ELSE 0 END) AS event_count,
  SUM(CASE WHEN l.log_type = 'protect' AND l.log_action IN ('modify', 'protect') THEN 1 ELSE 0 END) AS protect_count,
  SUM(CASE WHEN l.log_type = 'protect' AND l.log_action = 'unprotect' THEN 1 ELSE 0 END) AS unprotect_count,
  SUM(CASE WHEN l.log_type = 'protect' AND l.log_action = 'modify' THEN 1 ELSE 0 END) AS modify_count,
  SUM(CASE WHEN l.log_type = 'block' THEN 1 ELSE 0 END) AS block_count,
  SUM(CASE WHEN l.log_type = 'block' AND l.log_action = 'unblock' THEN 1 ELSE 0 END) AS unblock_count,
  SUM(CASE WHEN l.log_type = 'block' AND l.log_action = 'reblock' THEN 1 ELSE 0 END) AS reblock_count,
  SUM(CASE WHEN l.log_type = 'rights' THEN 1 ELSE 0 END) AS rights_count
FROM user AS u
JOIN user_groups AS ug ON u.user_id = ug.ug_user
LEFT JOIN logging AS l ON l.actor_user = u.user_id
WHERE ug.ug_group = 'sysop' AND u.user_name != 'مرشح الإساءة'
GROUP BY u.user_name
ORDER BY 
  u.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;
SQL

Checking query status...
