# inactive patrollers in the last year

SELECT CONCAT('*[[Special:Log/?type=patrol&user=',user_name,']]')

FROM user 

JOIN user_groups 

ON user_id = ug_user 

WHERE ug_group = 'patroller'

AND user_id NOT IN (

  SELECT DISTINCT user_id 

  FROM user_groups JOIN user ON user_id = ug_user 

  LEFT JOIN logging ON user_id = log_user 

  WHERE ug_group = 'patroller'

  AND log_action = 'reviewed' 

  AND log_timestamp > (NOW() - INTERVAL 1 YEAR)

  )

​

​