wikidata:Deleted trwiki pages by interwiki

SQL

USE wikidatawiki_p;
SELECT /*trwpl.pl_title, */REPLACE(comment_text, "/* clientsitelink-remove:1||trwiki */ ","") AS title, wdentity.page_title, COUNT(DISTINCT ips_site_id) as cnt, 
GROUP_CONCAT(DISTINCT ips_site_id SEPARATOR ", ") AS interwikis#, 
#(CASE WHEN ips_site_id="enwiki" THEN ips_site_page ELSE NULL END) AS enwiki_title,
#(CASE WHEN ips_site_id="enwiki" THEN enp.page_len ELSE NULL END) AS enwiki_len
FROM revision_userindex 
JOIN page wdentity ON rev_page=wdentity.page_id
JOIN comment_revision on rev_comment_id=comment_id AND comment_text LIKE "/* clientsitelink-remove:1||trwiki */ %"
JOIN actor_revision ON actor_id = rev_actor AND actor_name IN (SELECT user_name FROM trwiki_p.user_groups INNER JOIN trwiki_p.user ON user_id = ug_user WHERE ug_group = 'sysop')
#JOIN trwiki_p.pagelinks trwpl ON 
#trwpl.pl_title=REPLACE(REPLACE(comment_text, "/* clientsitelink-remove:1||trwiki */ ","")," ","_")
#AND trwpl.pl_from = 2219492 AND trwpl.pl_from_namespace = 4 AND trwpl.pl_namespace = 0
LEFT JOIN wb_items_per_site ON ips_item_id=REPLACE(wdentity.page_title,"Q","") AND ips_site_id REGEXP "^[a-z]{2,3}_?(?:[a-z]{2,3})?wiki$"
#LEFT JOIN enwiki_p.page AS enp ON enp.page_title=REPLACE(ips_site_page, " ", "_") AND ips_site_id = "enwiki"
WHERE 
NOT EXISTS
(SELECT p1.page_title FROM trwiki_p.page AS p1
WHERE p1.page_title = REPLACE(REPLACE(comment_text, "/* clientsitelink-remove:1||trwiki */ ","")," ","_")
#AND trwpl.pl_namespace = 0
#AND p1.page_namespace = 0
)
AND rev_timestamp <= (SELECT TIMESTAMP("2020-12-31 23:59:59"))
AND rev_timestamp >= (SELECT TIMESTAMP("2020-01-01 00:00:00"))
GROUP BY title HAVING interwikis NOT RLIKE "\btrwiki\b"
ORDER BY cnt DESC;
SQL

Checking query status...
