[unfinished]meta admins by actions

SQL

-- select TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))

use metawiki_p;
select 
	ug_user,
    user_name,
    `actions (usually admin rights logs)`,
    edits,
    `actions (all logs)`,
    case when `actions (usually admin rights logs)` < 10 and edits < 10 then 1 else 0 end as fails
    from
    (
      select 
          ug_user,
          user_name,
          (
            select count(
              case when 
              (
                (log_type = "delete" and log_action = "delete_redir") -- exclude redirect deletes - can be performed by anyone
                or (log_type = "patrol" and log_action = "autopatrol") -- autopatroll is what any autopatrolled could do
                or (log_type = "rights" and log_title like "%@%") -- global rights - not meta scope actions
              )
              then NULL
              else 1
              end
            )
            from logging_userindex
            where log_user = user_id 
            and log_timestamp > TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
            and log_type not in ("gblblock", "gblrights", "globalauth", "lock", "move", "newusers", "renameuser", "thanks", "upload", "review")
           ) as `actions (usually admin rights logs)`,
          (
            select count(*)
            from revision_userindex
            where rev_user = user_id
            and rev_timestamp > TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
          ) as edits,
          (
            select count(*)
            from logging_userindex
            where log_user = user_id 
            and log_timestamp > TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
           ) as `actions (all logs)`


      from user_groups
      join `user`
          on ug_group = "sysop"
          and ug_user = user_id
) as resultset
order by fails desc, `actions (usually admin rights logs)`, edits, `actions (all logs)` desc
SQL

Checking query status...
