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
          page_id,
    	  LOWER(CONVERT(SUBSTRING_INDEX(domain, '.', -1) USING utf8)) AS tld
      FROM (
          -- Extract all (page_id, domain) tuples.
          SELECT
              el_from AS page_id,
              REGEXP_REPLACE(el_to, '^(?:[^:/]+:)?//((?:[_0-9A-Za-z]+\\.)+[A-Za-z]+)(?:[/:?].*)?$', '\\1') AS domain
          FROM externallinks
          WHERE
              -- Only links from main namespace articles.
              el_from_namespace = 0
              -- Get only fairly well-formed URLs for http and https (or implicit https?) and exclude IPv4.
              AND el_to REGEXP '^(https?:)?//(?:[_0-9A-Za-z]+\\.)+[A-Za-z]+(?:[/:?].*)?$'
      ) AS domains
  ) AS tlds
  GROUP BY tld
) AS results
ORDER BY article_count DESC, total_count DESC;

SQL

Checking query status...