USE fawiki_p;

SELECT user_name, COUNT(log_id) cnt

FROM user JOIN logging ON log_user = user_id LEFT JOIN user_groups ON log_user = ug_user AND ug_group = 'bot'

WHERE log_type ='patrol'

AND log_action='patrol'

AND (

  log_params LIKE '%auto";i:0;}'

  OR log_params LIKE '%0'

  )

AND log_title IN (

  SELECT page_title

  FROM page

  WHERE page_namespace = 0

)

AND ug_group IS NULL

AND  NOW() - INTERVAL 1 YEAR <rev_timestamp

GROUP BY user_name

ORDER BY cnt DESC;