Fork of Items with many interwikis but without a Czech interwiki by Jan Spousta
This query is marked as a draft This query has been published by Mr. Ibrahem.

SQL

x
 
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', '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 > 30) 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 = 'arwiki'   ) 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 = 'enwiki' ) 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
;
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...