USE wikidatawiki_p;

SELECT trwpl.pl_title, wdentity.page_title, COUNT(DISTINCT wips.ips_site_id) as cnt, 

GROUP_CONCAT(DISTINCT wips.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

JOIN actor_revision ON actor_id = rev_actor AND actor_name="Vito Genovese"

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 AS wips ON

wips.ips_item_id=REPLACE(wdentity.page_title,"Q","") 

AND wips.ips_site_id REGEXP "^[a-z]{2,3}_?(?:[a-z]{2,3})?wiki$"

AND NOT EXISTS (SELECT ips_item_id FROM wb_items_per_site AS wips1 WHERE 

                wips1.ips_item_id=wips.ips_item_id

                AND wips1.ips_site_id="trwiki")

#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 = trwpl.pl_title

AND trwpl.pl_namespace = 0

AND p1.page_namespace = 0)

AND rev_timestamp <= (SELECT TIMESTAMP("2017-03-29 16:26:55"))

AND rev_timestamp >= (SELECT TIMESTAMP("2017-03-29 15:48:47"))

GROUP BY trwpl.pl_title

ORDER BY cnt DESC;