USE trwiki_p;

SELECT NOW() as updated;

​

SET @N = -100; # -gün sayısı

SET @M = 100;  # son -N günde maksimum edit sayısı

​

SELECT CONCAT("[[User:",user_name,"|]]") as kullanici, last_edit as son_katki, user_editcount as katki_sayisi, DATEDIFF(NOW(), last_edit) as gun

, ug_group as grup, lal.ec

    FROM (SELECT *, MAX(rev_timestamp) AS last_edit FROM revision_userindex WHERE rev_user > 0 GROUP BY rev_user) AS latest

    JOIN user

        ON user_id = latest.rev_user

     JOIN user_groups

        ON ug_user = latest.rev_user

        left join 

(SELECT *, count(rev_timestamp) as ec from revision_userindex where rev_user>0 and rev_timestamp > DATE_ADD(NOW(), INTERVAL @N DAY) 

 group by rev_user having count(rev_timestamp)<@M) as lal

  on lal.rev_user = latest.rev_user

​

WHERE (ug_group = 'sysop' OR 'ug_group' = 'bureaucrat')

AND (last_edit < DATE_ADD(NOW(), INTERVAL @N DAY) OR user_id in 

(SELECT rev_user from revision_userindex where rev_user>0 and rev_timestamp > DATE_ADD(NOW(), INTERVAL @N DAY) 

 group by rev_user having count(rev_timestamp)<@M))

ORDER BY lal.ec ASC, last_edit ASC

;

SELECT NOW();