People_murdered_abroad_by_nationality

Fork of Television_by_country by Hosseinronaghi

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

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 = 'fa'
  where cl_to in (
    select page_title
    from categorylinks
    join page
      on page_id = cl_from
    where cl_to = '2021'
  )
) t
join page
  on page_title = cl_to
  and page_namespace = 14 -- category
left join langlinks
  on ll_from = page_id
  and ll_lang = 'fa'
where ll_title is null
group by cl_to
order by count(*) desc;

SQL

Checking query status...