USE zhwiki_p; 

SELECT

    count(*) AS counter,

    user_name,

    user_editcount

​

FROM logging log

​

JOIN user us

  ON log.log_user = us.user_id

​

JOIN user_groups ug

  ON log.log_user = ug.ug_user

​

WHERE ug.ug_user NOT IN

  (SELECT DISTINCT user_groups.ug_user

   FROM user_groups

   WHERE ug_group = 'sysop'

   OR ug_group = 'rollbacker'

  )

AND log.log_type = 'patrol'

AND log.log_action = 'patrol'

AND log.log_timestamp > ( NOW() - INTERVAL 24 MONTH )  

AND ( log.log_params LIKE '%"6::auto";i:0%'

OR    log.log_params LIKE '%\n0' /* not autopatrolled */ )

GROUP BY log.log_user

​

ORDER BY counter DESC

LIMIT 50;