This query is marked as a draft This query has been published by Base.

SQL

​x
 
-- select TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
​
use metawiki_p;
select 
    ug_user,
    user_name,
    edits,
    `actions (usually admin rights logs)` + ns8edits + `actions (CN)` as adminactions,
    ns8edits,
    `actions (usually admin rights logs)`,
    `actions (all logs)`,
    `actions (CN)`,
    case 
        when edits < 10  then "autofail"
        when (`actions (usually admin rights logs)` + ns8edits + `actions (CN)`) < 10 then "notification" 
        else "passed"
        end as review
    from
    (
      select 
          ug_user,
          user_name,
          (
            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 revision_userindex
            join page 
                  on rev_timestamp > TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
                  and rev_page = page_id
                  and page_namespace = 8
            where rev_user = ug_user
          ) as ns8edits,
          (
            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 logging_userindex
            where log_user = user_id 
            and log_timestamp > TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
           ) as `actions (all logs)`,
          (
            select count(*)
            from cn_notice_log
            where notlog_user_id = user_id 
            and notlog_timestamp > TIMESTAMP(DATE_SUB("2017-10-01", INTERVAL 6 MONTH))
           ) as `actions (CN)`
      
      from user_groups
      join `user`
          on ug_group = "sysop"
          and ug_user = user_id
) as resultset
order by review asc, adminactions, edits, `actions (all logs)` 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
All SQL code is licensed under CC0 License.

Checking query status...