Users locked by WMF HR but not listed as former staff

Fork of Users locked by WMF HR but not listed as former staff by Nemo bis

This query is marked as a draft This query has been published by BStorm (WMF).

Example query to make updates easier for
https://meta.wikimedia.org/wiki/Category:Former_Wikimedia_Foundation_staff_accounts
https://meta.wikimedia.org/wiki/Template:WMF_Staff

SQL

USE metawiki_p;
SELECT actor_name, REPLACE(log_title, "@global", "") AS username, log_timestamp, log_comment
FROM logging_compat
JOIN actor
ON log_actor = actor_id
AND actor_name LIKE "%(WMF)"
AND log_action = "setstatus"
AND log_params LIKE "%locked%"
JOIN centralauth_p.globaluser gu
ON REPLACE(gu.gu_name, " ", "_") = REPLACE(log_title, "@global", "")
AND gu.gu_name LIKE '%WMF)'
AND gu.gu_locked = 1
GROUP BY username
HAVING username NOT IN (
  SELECT page_title
  FROM page
  JOIN categorylinks ON page_id = cl_from
  AND cl_to = "Former_Wikimedia_Foundation_staff_accounts");

SQL

Checking query status...