ckb: User pages of IPs with no contributions or logs

SQL

SELECT CONCAT('https://meta.wikimedia.org/wiki/User', CASE WHEN p.page_namespace = 3 THEN '_talk:' ELSE ':' END, p.page_title) AS URL
FROM metawiki_p.page p
LEFT JOIN metawiki_p.actor a ON a.actor_user IS NULL AND a.actor_name = SUBSTRING_INDEX(p.page_title, '/', 1)
WHERE p.page_namespace = 2
AND p.page_is_redirect = 0
AND a.actor_id IS NULL
AND (
  IS_IPV4(SUBSTRING_INDEX(p.page_title, '/', 1))
  OR
  IS_IPV6(SUBSTRING_INDEX(p.page_title, '/', 1))
)
AND NOT EXISTS (
  SELECT l.log_id
  FROM metawiki_p.logging_logindex l
  WHERE l.log_title = SUBSTRING_INDEX(p.page_title, '/', 1)
  AND l.log_namespace = 2
)

SQL

Checking query status...