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;
*/
USE cswiki_p;
SELECT COUNT(IF(page_title IS NULL, 0, 1)) / COUNT(*)#afl_actions, afl_namespace, afl_title, page_title, page_namespace
FROM (SELECT DISTINCT afl_title, afl_namespace FROM abuse_filter_log WHERE afl_filter = 81 AND afl_timestamp > 20151107103900 AND afl_actions = '') AS afl
LEFT JOIN page ON page_title = afl_title AND page_namespace = afl_namespace;
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...
