USE fawiki_p;

SELECT rev_user_text, Count(*) cnt, CASE WHEN ufg_group IS NULL THEN '' ELSE '{{خیر|بله}}' END AS lostautopatrol FROM revision LEFT JOIN user_former_groups ON rev_user = ufg_user AND ufg_group = 'autopatrolled' left join page on rev_page = page_id WHERE rev_parent_id = 0 and page_is_redirect = 0 and page_namespace = 0 AND rev_timestamp > CASE WHEN Month(Curdate()) = 1 THEN Concat(Year(Curdate()) - 1, '12', Lpad(Day( Curdate()), 2, 0), '000000') ELSE Concat(Year(Curdate()), Lpad( Month(Curdate()) - 1, 2, 0), Lpad(Day(Curdate()), 2, 0), '000000') END AND rev_user NOT IN (SELECT ug_user FROM user_groups WHERE ug_group IN ( 'autopatrolled', 'sysop', 'bot' )) AND rev_user <> 0 /* anonymous users */ AND rev_user <> 374638 /* پیام به کاربر جدید */ GROUP BY rev_user_text, ufg_group HAVING cnt > 20 ORDER BY cnt DESC;