Fork of wikidata:Unexisting trwiki Mirada articles by interwiki by Evolutionoftheuniverse
This query is marked as a draft This query has been published by Evolutionoftheuniverse.

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.

Checking query status...