SQL
AخA
USE wikidatawiki_p;
SELECT trwpl.pl_title, wdentity.page_title, ips_site_page AS enwiki_title, enp.page_len AS enwiki_len, GROUP_CONCAT(DISTINCT pap_project_title SEPARATOR ", ") AS enprojects, GROUP_CONCAT(DISTINCT trproj_page.ll_title SEPARATOR ", ") AS trprojects
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
JOIN wb_items_per_site ON ips_item_id=REPLACE(wdentity.page_title,"Q","") AND ips_site_id="enwiki"
JOIN enwiki_p.page AS enp ON enp.page_title=REPLACE(ips_site_page, " ", "_") AND enp.page_namespace=0 AND enp.page_is_redirect=0
LEFT JOIN enwiki_p.page_assessments ON enp.page_id=pa_page_id
LEFT JOIN enwiki_p.page_assessments_projects ON pa_project_id=pap_project_id
LEFT JOIN page AS enproj_page ON pap_project_title=REPLACE(REPLACE(enproj_page.page_title,"WikiProject_",""),"_"," ") AND enproj_page.page_title LIKE "WikiProject_%" AND enproj_page.page_namespace = 4
LEFT JOIN langlinks AS trproj_page ON trproj_page.ll_from=enproj_page.page_id AND ll_lang="tr"
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 enwiki_len DESC;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.