wikidata:Unexisting trwiki Mirada articles with enwiki interwiki

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.

Whole query (2358) https://quarry.wmflabs.org/run/509138/output/0/html
Top-importance (253) https://quarry.wmflabs.org/run/509586/output/0/html
High-importance (613) https://quarry.wmflabs.org/run/509587/output/0/html
Mid-importance (1008) https://quarry.wmflabs.org/run/509594/output/0/html
Low-importance (1049) https://quarry.wmflabs.org/run/509595/output/0/html
Unknown-importance (1834) https://quarry.wmflabs.org/run/509593/output/0/html
FA-class (12) https://quarry.wmflabs.org/run/509598/output/0/html
GA-class (32) https://quarry.wmflabs.org/run/509599/output/0/html
A-class (2) https://quarry.wmflabs.org/run/509601/output/0/html
B-class (207) https://quarry.wmflabs.org/run/509602/output/0/html
History (481) https://quarry.wmflabs.org/run/509169/output/0/html
Biography (1234) https://quarry.wmflabs.org/run/509170/output/0/html
Law, human rights, politics, policy, international relations (423) https://quarry.wmflabs.org/run/509197/output/0/html
Culture, myth, religion (83) https://quarry.wmflabs.org/run/509173/output/0/html
Literature (170) https://quarry.wmflabs.org/run/509166/output/0/html
Crime (23) https://quarry.wmflabs.org/run/509177/output/0/html
STEM (156)* https://quarry.wmflabs.org/run/509190/output/0/html
Philosophy (31) https://quarry.wmflabs.org/run/509193/output/0/html
Turkey, Ottoman (51) https://quarry.wmflabs.org/run/509200/output/0/html
Medicine, health, physiology, pharmacology (27) https://quarry.wmflabs.org/run/509569/output/0/html
Women (74) https://quarry.wmflabs.org/run/509581/output/0/html
War, military (309) https://quarry.wmflabs.org/run/509585/output/0/html
Geography, city, place (230) https://quarry.wmflabs.org/run/509606/output/0/html
Country (80) https://quarry.wmflabs.org/run/509609/output/0/html
State (129) https://quarry.wmflabs.org/run/509611/output/0/html
Europe (164) https://quarry.wmflabs.org/run/509616/output/0/html
Vatican (7) https://quarry.wmflabs.org/run/509617/output/0/html

"*": AND pap_project_title REGEXP "(?:[Ss]cience|[Tt]ech|[Ee]ngineer|[Mm]ath|[Pp]hysic|[Cc]hem|[Bb]iol|[Ss][Tt][Ee][Mm]|[Gg]eolog|[Mm]eteo)"

SQL

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 CONCAT(pap_project_title, "(",pa_class,", ",pa_importance,")") SEPARATOR "; ") AS enprojects, COUNT(DISTINCT pap_project_title) AS no_of_projects#, 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 enwiki_p.page AS enproj_page ON enproj_page.page_title="WikiProject_"+REPLACE(pap_project_title," ","_") AND enproj_page.page_title LIKE "WikiProject_%" AND enproj_page.page_namespace = 4 AND enproj_page.page_is_redirect=0
#LEFT JOIN enwiki_p.langlinks AS trproj_page ON trproj_page.ll_from=enproj_page.page_id AND trproj_page.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"))
AND pap_project_title REGEXP "(?:[Ee]ur)" AND pap_project_title NOT REGEXP "(?:Neur)"
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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...