Untitled query #50111

SQL

use enwiki_p;
select
  CONCAT('Category:', cl_to) as category_name,
  count(*) as fawiki_counterparts
from (
  select cl_to
  from categorylinks
  join page
    on page_id = cl_from
  join langlinks
    on ll_from = cl_from
    and ll_lang = ' '
  where cl_to in (
    select page_title
    from categorylinks
    join page
      on page_id = cl_from
    where cl_to = 'People_by_nationality'
  )
) t
join page
  on page_title = cl_to
  and page_namespace = 14 -- category
left join langlinks
  on ll_from = page_id
  and ll_lang = 'en'
where ll_title is null
group by cl_to
order by count(*) desc;
SQL

Checking query status...
