enwikiのFA記事のjawikiにおける現状一覧

SQL

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
SQL

Checking query status...
