Top TLDs used in external links (eswiki, main namespace) v1

This query shows TLDs used in external links from the main namespace of Spanish Wikipedia, along with a count of how many links they appear in, as well as the number of articles containing, at least, one link with the given TLD.

Only links with scheme http, https, ftp or relative (//) are considered. That excludes other such as irc://, mailto: or svn://.

WARNING: This query does not support IDN TLDs.

SQL

USE eswiki_p;
SELECT *
FROM (
  SELECT
      tld,
      COUNT(DISTINCT page_id) AS article_count,
      COUNT(*) AS total_count
  FROM (
    -- Extract all (page_id, tld) tuples.
    SELECT
    	el_from AS page_id,
    	LOWER(CONVERT(REGEXP_REPLACE(SUBSTRING(el_index FROM 1 FOR 60), '(?i)^(?:https?:|ftp:)?//([a-z]+)\\..*?$', '\\1') USING utf8)) AS tld
    FROM externallinks
    WHERE
    -- Only links from main namespace articles.
    el_from_namespace = 0
    -- Only http, https, implicit https (//) and ftp. el_index is already lowercase.
    AND (el_index LIKE 'http://%' OR el_index LIKE 'https://%' OR el_index LIKE '//%' OR el_index LIKE 'ftp://%')
    -- Get only fairly well-formed URLs and exclude IPv4.
    AND el_to REGEXP '(?i)^(https?:)?//(?:[-_0-9A-Za-z]+\\.)+[A-Za-z]+(?:[/:?].*)?$'
  ) AS tlds
  GROUP BY tld
) AS results
ORDER BY article_count DESC, total_count DESC;

SQL

Checking query status...