T220794 - Quarry

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

SQL

USE cswiki_p;
SELECT uncategorized.page_namespace, uncategorized.page_title FROM page AS uncategorized
LEFT JOIN categorylinks ON cl_from = uncategorized.page_id
LEFT JOIN page AS categories ON cl_to = categories.page_title AND categories.page_namespace = 14
LEFT JOIN page_props ON pp_page = categories.page_id AND pp_propname = "hiddencat"
WHERE uncategorized.page_is_redirect = 0
AND uncategorized.page_namespace IN (0, 10, 12, 14)
GROUP BY uncategorized.page_namespace, uncategorized.page_title
HAVING SUM(IF(pp_propname = "hiddencat", 1, 0)) = cats
ORDER BY uncategorized.page_namespace, uncategorized.page_title;
/*
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;

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;
*/

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...