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

This query shows domains 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 to the domain.

Domains are simplified to the main domain:
* news.bbc.com -> bbc.com
* news.bbc.co.uk -> bbc.co.uk
* www.dover.nj.us -> dover.nj.us

Top 2000 are shown, according to number of articles containing links to the domain.

SQL

USE eswiki_p;
SELECT
	main_domain,
	COUNT(DISTINCT page_id) AS article_count,
	COUNT(*) AS total_count
FROM (
	-- Extract all (page_id, main_domain) tuples
	SELECT
		page_id,
    	-- Extract main_domain from domain.
    	-- Examples:
    	--   news.bbc.com -> bbc.com
    	--   news.bbc.co.uk -> bbc.co.uk
    	IF(domain REGEXP '^.*[[:alnum:]]{4,}\.[[:alnum:]]{2,}$',
           SUBSTRING_INDEX(domain, '.', -2),
           IF(domain REGEXP '^.*\.(ac|edu|org|or|com|co|net|gov|go)\.[[:alnum:]]{2,}$',
              SUBSTRING_INDEX(domain, '.', -3),
              IF(domain REGEXP '.*\.[[:alnum:]]{2}\.(us|ca|za)$',
                 SUBSTRING_INDEX(domain, '.', -3),
                 SUBSTRING_INDEX(domain, '.', -2)
                ))) AS main_domain
    FROM (
        -- Extract all (page_id, domain) tuples.
        SELECT
            el_from AS page_id,
            SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(
            CAST(el_to AS CHAR(1000) CHARACTER SET utf8),
            '/', 3), '://', -1), '/', 1), '?', 1), ':', 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 and exclude IPv4.
            AND el_to REGEXP '^https?://[[:alnum:]]+\..*[[:alpha:]].*'
        LIMIT 10
    ) AS domains
) AS main_domains
GROUP BY main_domain
ORDER BY article_count DESC, total_count DESC;

SQL

Checking query status...