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.