SQL
AخA
use jawiki_p;
select
enwiki_pagename,
langs,
enwiki_pagelen,
ja_page.page_title,
ja_cat.cl_to,
ja_page.page_len,
ROUND( (ja_page.page_len / enwiki_pagelen), 2) as size_ratio
from page as ja_page
join page_props as ja_pp on page_id = pp_page and pp_propname = "wikibase_item"
left join categorylinks as ja_cat on cl_from = page_id and ( cl_to = "良質な記事" or cl_to = "秀逸な記事" )
join (
select
pp_value,
page_title as enwiki_pagename,
page_len as enwiki_pagelen,
count(*) as langs
from wikidatawiki_p.wb_items_per_site
join (
select page_id, page_title, page_len, pp_value, substring( pp_value, 2) as wikidatalink
from enwiki_p.categorylinks
join enwiki_p.page_props on cl_from = pp_page and pp_propname = "wikibase_item"
join enwiki_p.page on cl_from = page_id
where
cl_to = "Featured_articles"
) as enwiki_FAs on wikidatalink = ips_item_id
group by page_title, page_len, pp_value
) as en on ja_pp.pp_value = en.pp_value
order by (ja_page.page_len / enwiki_pagelen) asc
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.