for autoreview or blocking on trwiki

Lists the non-blocked registered users on trwiki not attached to any user groups (such as sysop). The users that have never been part of any user group will be listed on top sorted according to their number of contributions in the last 100 days. The columns are for user names, last contribution date,  #contributions in the last 100 days, total # contributions, user registration date, former user groups, link to wiki user contribution page.

Beyaz listeye ekleme ya da engellemede faydalı olsun diye yapılmıştır. Engellenmemiş kayıtlı kullanıcıları listeler. Hiç bir zaman bir kullanıcı grubuna dahil olmamış kullanıcılar üstte, son 100 günde yaptıkları toplam değişiklik sayısına göre sıralanır.

SQL

USE trwiki_p;
SELECT NOW() as guncelleme_zamani;
 

SELECT CONCAT("[[Kullanıcı:",user_name,"|",user_name,"]]") AS kullanici, date_format(str_to_date(last_edit, '%Y%m%d%H%i%s'), '%e %M %Y %H:%i') AS son_katki, saysay as katki_sayisi_100, 
      user_editcount AS katki_sayisi_toplam, date_format(str_to_date(user_registration, '%Y%m%d%H%i%s'), '%e %M %Y') AS kullanici_kayit_tarihi, ufg_group as eski_grup, 
CONCAT("[[%C3%96zel:Katk%C4%B1lar/", user_name,"]]") AS link
	FROM (SELECT rev_user, COUNT(rev_timestamp) AS saysay, MAX(rev_timestamp) AS last_edit FROM revision WHERE rev_user > 0 AND rev_timestamp > DATE_ADD(NOW(), 
              INTERVAL -100 DAY) GROUP BY rev_user) AS latest
          JOIN user ON user_id = latest.rev_user
          LEFT JOIN user_groups ON ug_user = latest.rev_user
          LEFT JOIN ipblocks ON ipb_user = latest.rev_user
          LEFT JOIN user_former_groups ON ufg_user = latest.rev_user
WHERE ipb_user IS NULL
AND ug_user IS NULL
AND user_editcount > 30
AND user_registration < date_add(now(), INTERVAL -7 day)
AND user_name NOT LIKE '%Bot'
AND user_name NOT LIKE '%BOT'
ORDER BY ufg_group, saysay DESC, user_editcount DESC
LIMIT 500;
SELECT NOW();

SQL

Checking query status...