Test 3 - Quarry

SQL

USE ckbwiki_p;

SELECT
  ROW_NUMBER() OVER (ORDER BY last_edit.last_edit_date ASC) AS row_number,
  user_name,
  user_registration,
  user_editcount,
  last_edit.last_edit_date,
  GROUP_CONCAT(ug_group) AS groups
FROM
  user
LEFT JOIN (
  SELECT
    actor_user,
    MAX(rev_timestamp) AS last_edit_date
  FROM
    revision
  JOIN
    actor ON rev_actor = actor_id
  GROUP BY
    actor_user
) AS last_edit ON user_id = last_edit.actor_user
LEFT JOIN user_groups ON user_id = ug_user
WHERE
  user_editcount > 0 -- Adjust the edit count threshold as needed
  AND (last_edit.last_edit_date IS NULL OR last_edit.last_edit_date <= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 MONTH), "%Y%m%d%H%i%S"))
  AND user_id NOT IN (
    SELECT
      ug_user
    FROM
      user_groups
    WHERE
      ug_group LIKE '%bot%'
  ) -- Exclude bot user groups
  AND NOT user_name RLIKE '[Bb][Oo][Tt]$' -- Exclude users with "bot" at the end in a case-insensitive manner
  AND user_name != 'KLBot2' -- Exclude the specific username "KLBot2"
GROUP BY user_id
ORDER BY
  last_edit.last_edit_date ASC
LIMIT 100;
SQL

Checking query status...
