Untitled query #1276

SQL

# test
# See also https://upload.wikimedia.org/wikipedia/commons/f/f7/MediaWiki_1.24.1_database_schema.svg
use wikidatawiki_p; 

SELECT pp_value, COUNT(ips_site_id) as cnt, GROUP_CONCAT(REPLACE(ips_site_id,"wiki","") ORDER BY ips_site_id) as sites 
FROM wb_items_per_site, enwiki_p.categorylinks
JOIN enwiki_p.page_props ON pp_page=cl_from 
WHERE cl_to IN 
    ("1990_deaths")
    #("2000_deaths", "2001_deaths", "2002_deaths", "2003_deaths", "2004_deaths", "2005_deaths", "2006_deaths", "2007_deaths", "2008_deaths", "2009_deaths", "2010_deaths", "2011_deaths", "2012_deaths", "2013_deaths", "2014_deaths", "2015_deaths")
	AND pp_propname="wikibase_item" 
	AND cl_type="page" 
 	AND pp_value NOT IN (SELECT page_title FROM wikidatawiki_p.page JOIN wikidatawiki_p.pagelinks 
    	                  ON page_id=pl_from WHERE pl_title = "P570" AND pl_namespace = 120)
 AND pp_value = epp_entity_id
 GROUP BY pp_value 
 HAVING COUNT(ips_site_id) >1 
 ORDER BY COUNT(ips_site_id) DESC 
 LIMIT 900
SQL

Checking query status...
