SELECT lt.lt_title AS Target, COUNT(IF(page_title IS NULL, 1, NULL)) AS LinkCount

FROM pagelinks pl

INNER JOIN linktarget lt ON pl.pl_target_id = lt.lt_id

LEFT JOIN page ON page_namespace = 0 AND page_title = lt.lt_title

WHERE pl_from_namespace = 0 AND lt.lt_namespace = 0

-- Exclude some results from templates: United states elections, years in Ireland and France, some classifications of bacteria

-- AND lt.lt_title NOT LIKE '%_United_States_elections%' AND lt.lt_title NOT LIKE '%_in_Ireland%' AND lt.lt_title NOT LIKE '%_in_France%' AND lt.lt_title NOT LIKE '%bacteriota%'AND lt.lt_title NOT LIKE '%bacterota%'

AND NOT EXISTS (

    -- exclude links from templates

    SELECT 1

    FROM templatelinks tl

    WHERE tl.tl_from = pl.pl_from

    AND tl.tl_from_namespace = pl.pl_from_namespace

    AND tl.tl_target_id = pl.pl_target_id

)

GROUP BY Target

​

LIMIT 100;