Ações de administradores na Wikipédia lusófona em 2021

SQL

SELECT
 actor_name Usuário,
 SUM(IF((log_type, log_action) IN (('delete', 'delete'), ('delete', 'delete_redir')), actnum, 0)) Eliminações,
 SUM(IF((log_type, log_action) = ('delete', 'restore'), actnum, 0)) Restauros,
 SUM(BLOCK24H) Bloq_até_24h,
 SUM(IF((log_type, log_action) = ('block', 'block'), actnum - BLOCK24H, 0)) Bloq_mais_24h,
 SUM(IF((log_type, log_action) = ('block', 'reblock'), actnum, 0)) Modif_bloq,
 SUM(IF((log_type, log_action) = ('block', 'unblock'), actnum, 0)) Desbloq,
 SUM(IF((log_type, log_action) = ('delete', 'revision'), actnum, 0)) Supressão,
 SUM(IF((log_type, log_action) = ('protect', 'protect'), actnum, 0)) Proteções,
 SUM(IF((log_type, log_action) = ('protect', 'unprotect'), actnum, 0)) Desprot,
 SUM(IF((log_type, log_action) = ('protect', 'modify'), actnum, 0)) Modif_prot
FROM (
 SELECT
  actor_name,
  log_type,
  log_action,
  SUM((log_type, log_action) = ('block', 'block') AND log_params RLIKE '"\\d+ (hour|day)s?"') BLOCK24H,
  COUNT(*) actnum
  FROM logging
  INNER JOIN actor ON actor_id = log_actor
  WHERE log_timestamp > 20210000000000
   AND actor_user IN (SELECT ug_user FROM user_groups WHERE ug_group = 'sysop')
   AND (log_type, log_action) IN (('delete', 'delete'), ('delete', 'restore'), ('delete', 'delete_redir'),
                                  ('block', 'block'), ('block', 'reblock'), ('block', 'unblock'),
                                  ('delete', 'revision'), ('protect', 'protect'), ('protect', 'unprotect'),
                                  ('protect', 'modify'))
  GROUP BY actor_name, log_type, log_action
 ) x
 GROUP BY Usuário;
SQL

Checking query status...
