Untitled query #9702

SQL

use wikidatawiki_p; 

select ips_item_id,  nn as n_of_interwikis, en_title, page_id, page_title
from (
select  ips_item_id,  nn, en_title, concat('Q',trim(ips_item_id)) as qtitle
from 
(select ips_item_id, nn, cs_title
from
 (select ips_item_id, count(*) as nn
  from wb_items_per_site
  where ips_site_id in ('enwiki', 'dewiki', 'frwiki', 'eswiki', 'ruwiki', 'itwiki', 'jawiki', 'nlwiki', 
                        'ptwiki', 'plwiki', 'zhwiki', 'svwiki', 'shwiki', 'arwiki', 'fawiki', 'hewiki', 
                        'ukwiki', 'kowiki', 'huwiki', 'trwiki', 'cawiki', 'fiwiki', 'nowiki', 'cswiki',
                        'simplewiki', 'idwiki', 'srwiki', 'rowiki', 'dawiki', 'bgwiki', 'thwiki', 'skwiki', 
                        'eowiki', 'elwiki', 'euwiki', 'ltwiki', 'hrwiki', 'slwiki', 'etwiki', 'hywiki', 'mswiki', 
                        'glwiki', 'azwiki', 'kawiki', 'hiwiki', 'lvwiki', 'kkwiki', 'bewiki', 'mlwiki', 'bnwiki', 
                        'tawiki', 'sqwiki' )          # list of established Wikipedias            
  group by ips_item_id      
  having nn > 27) as grp left outer join # how many interwikis needed to list here
( select ips_item_id as ii, ips_site_page as cs_title
  from wb_items_per_site
  where ips_site_id = 'enwiki'   ) as cs 
  on ips_item_id = ii 
 ) as f  left outer join
( select ips_item_id as iii, ips_site_page as en_title
  from wb_items_per_site
  where ips_site_id = 'dewiki' ) as eng   
  on ips_item_id = iii
where  cs_title IS NULL 
  ) as rawpages left outer join page on page_title = qtitle 
where page_namespace = 0 and INSTR(en_title,':') = 0 # get rid of categories etc. 
#limit 100
;
SQL

Checking query status...
