T220794 - Quarry

This query is marked as a draft This query has been published by Matěj Suchánek.
SQL

/*
SELECT tl_title AS template, COUNT(DISTINCT tl_from) AS total, SUM(IF(pp_value IS NULL, 0, 1)) / COUNT(DISTINCT tl_from) AS having_item_rel,
	CONCAT("P", pi_property_id) AS prop, SUM(IF(pl_title IS NULL, 0, 1)) AS having_prop,
	SUM(IF(pl_title IS NULL, 0, 1)) / SUM(IF(pp_value IS NULL, 0, 1)) AS having_prop_rel
# WHERE tl_title LIKE "Infobox&_-&_%" ESCAPE "&" AND tl_title NOT LIKE "%/%" AND tl_from_namespace = 0
FROM (SELECT * FROM templatelinks WHERE tl_title = "Infobox_-_spisovatel" AND tl_from_namespace = 0) AS ibox
LEFT JOIN (SELECT * FROM page_props WHERE pp_propname = "wikibase_item") AS props ON pp_page = tl_from
LEFT JOIN wikidatawiki_p.wb_property_info ON pi_property_id NOT IN (143, 248, 548, 580, 582, 585, 642, 854)
LEFT JOIN wikidatawiki_p.page ON pp_value IS NOT NULL AND page_title = pp_value AND page_namespace = 0
LEFT JOIN wikidatawiki_p.pagelinks ON pl_from IS NOT NULL AND pl_from = page_id AND pl_namespace = 120 AND pl_title = CONCAT("P", pi_property_id)
GROUP BY tl_title, pi_property_id HAVING having_prop_rel > 0
ORDER BY having_prop_rel, having_prop, having_item_rel, total, tl_title;

SELECT DISTINCT pl_title, pl_namespace, COUNT(DISTINCT pl_from) AS linked_from, COUNT(DISTINCT log_id) AS times_deleted
FROM logging
JOIN pagelinks ON pl_title = log_title AND pl_namespace = log_namespace
LEFT JOIN page ON page_title = pl_title AND page_namespace = pl_namespace
WHERE log_type = "delete" AND log_action = "delete"
AND page_title IS NULL AND pl_from_namespace = 0
GROUP BY pl_title, pl_namespace
ORDER BY linked_from DESC, times_deleted DESC, pl_title, pl_namespace
LIMIT 500;
SELECT * FROM page AS uncategorized
JOIN categorylinks ON cl_from = page_id
JOIN page AS categories ON cl_to = categories.page_title AND categories.page_namespace = 14
LEFT JOIN (SELECT * FROM page_props WHERE pp_propname = "hiddencat") AS props ON pp_page = categories.page_id
HAVING SUM(IF(pp_propname = "hiddencat", 1, 0)) = COUNT(DISTINCT cl_to)
LIMIT 5;
USE cswiki_p;
SELECT pl_title, pl_namespace, COUNT(*) FROM pagelinks
#LEFT JOIN templatelinks
#	ON tl_from = pl_from AND tl_from_namespace = pl_from_namespace AND tl_title = pl_title AND tl_namespace = pl_namespace
WHERE #tl_title IS NULL AND
pl_namespace IN (2,3,5,7,8,9,11,13)
AND pl_from_namespace = 0
AND NOT EXISTS (SELECT * FROM page WHERE page_title = pl_title AND page_namespace = pl_namespace)
#AND pl_title NOT LIKE "Automobily%"
#AND pl_title NOT LIKE "Data_státu/%"
GROUP BY pl_title, pl_namespace
ORDER BY pl_namespace, COUNT(*), pl_title
LIMIT 200;*/
SELECT osn.page_title FROM (SELECT * FROM cswikisource_p.page WHERE page_namespace = 0 AND page_title LIKE "Ottův_slovník_naučný/%") AS osn
LEFT JOIN (SELECT * FROM cswikisource_p.page_props WHERE pp_propname = "wikibase_item") AS osn_props ON osn_props.pp_page = osn.page_id
LEFT JOIN (SELECT * FROM wikidatawiki_p.page WHERE page_namespace = 0) AS osn_items ON osn_items.page_title = osn_props.pp_value
LEFT JOIN (SELECT * FROM wikidatawiki_p.pagelinks WHERE pl_namespace = 120 AND pl_title = "P921") AS osn_links ON osn_links.pl_from = osn_items.page_id
JOIN cswiki_p.page AS pages ON pages.page_namespace = 0 AND CONCAT("Ottův_slovník_naučný/", pages.page_title) = osn.page_title
WHERE osn_links.pl_title IS NULL;
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...
