This query is marked as a draft This query has been published by 軽快.

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.

Checking query status...