Experienced users in idwiki (without rights)

Fork of Experienced users in zhwiki (without rights) by WhitePhosphorus

This query is marked as a draft This query has been published by Bennylin.

SQL

USE idwiki_p;
SELECT user_id, CONCAT('Pengguna:', user_name), user_editcount, user_registration, GROUP_CONCAT(ug_group)
FROM user
LEFT JOIN user_groups
ON user_id = ug_user
WHERE user_editcount >= 200
  AND (user_registration IS NULL
       OR TIMESTAMPDIFF(DAY, user_registration, CURRENT_TIMESTAMP) >= 30)
  AND user_id IN (SELECT rc_user FROM recentchanges WHERE rc_user = user_id)
  AND user_id NOT IN (SELECT ug_user FROM user_groups WHERE ug_user = user_id AND ug_group = "bot")
GROUP BY user_id
HAVING GROUP_CONCAT(ug_group) IS NULL OR 
         (GROUP_CONCAT(ug_group) NOT LIKE "%sysop%"
          AND GROUP_CONCAT(ug_group) NOT LIKE "%rollbacker%"
          AND GROUP_CONCAT(ug_group) NOT LIKE "%patroller%");

SQL

Checking query status...