Potentially extraneous homepage links (dewiki <= M)

SQL

USE dewiki_p; SET @language = 'de';
SELECT 
CONCAT('https://', @language , '.wikipedia.org/wiki/' ,page_title, '?action=edit&veswitched=1#:~:text=', rooturl) AS page_edit_link, 
# try to highlight homepage URL in source wikitext
rooturl,
SUM(IF( url=rooturl, 1, 0) ) AS rootlinks,
SUM(1) AS alllinks
FROM (
  SELECT
    page_title,
    el_to AS url,
    CONCAT(SUBSTRING_INDEX(el_to, '/', 3),'/') AS rooturl
  FROM externallinks, page
  WHERE el_from = page_id
  AND page_namespace = 0
  # restrict query to a subset of articles for performance reasons:
  # AND LEFT(UPPER(CONVERT(page_title USING utf8)), 1) <= 'A'
  )  AS pagelinks
GROUP BY page_title, rooturl
HAVING 
  # page has links to both homepage and other pages on the same site: 
  rootlinks > 0 AND rootlinks < alllinks
  # not interested in e.g. https://www.wikipedia.org/ on [[Wikipedia]]:
  AND LOCATE( REPLACE(LOWER(CONVERT(page_title USING utf8)), '_', ''), REPLACE(rooturl,'-','') ) = 0
  # on enwiki, frequently generated by the {{expand (language)}} templates:
  AND rooturl != 'https://translate.google.com/' 
LIMIT 200000
SQL

Checking query status...
