Index link count by root page on English Wiktionary

Fork of Links to some Chinese indices on English Wiktionary by Erutuon

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

SQL

select
	root_page_title,
    source.page_namespace as link_from_namespace,
    count(*) as link_count,
    link_target_exists
from (
    select
        substring_index(pl_title, '/', 1) as root_page_title,
        target.page_id is not null as link_target_exists,
        pl_from
    from pagelinks
    left join page as target on target.page_namespace = pl_namespace and target.page_title = pl_title
    where pl_namespace = 104 /* Index */
) as index_links
join page as source on source.page_id = pl_from
group by source.page_namespace, index_prefix, link_target_exists
order by link_count desc;

SQL

Checking query status...