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

This query shows domains used in external links from the main namespace of Euskera 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 500 are shown, according to number of articles containing links to the domain.

SQL

USE euwiki_p;
SELECT *
FROM (
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
		CASE
        WHEN domain LIKE '%.gouv.fr' THEN SUBSTRING_INDEX(domain, '.', -3)
      	WHEN domain LIKE '%.govt.nz' THEN SUBSTRING_INDEX(domain, '.', -3)
		WHEN domain REGEXP '^.*[-\\w]{4,}\\.[[:alpha:]]{2,}$' THEN SUBSTRING_INDEX(domain, '.', -2)
		WHEN domain REGEXP '^.*\\.(ac|edu|org|or|com|co|net|gov|gob|go)\\.[[:alpha:]]{2,}$' THEN SUBSTRING_INDEX(domain, '.', -3)
		WHEN domain REGEXP '.*\\.[[:alpha:]]{2}\\.(us|ca|za|it)$' THEN SUBSTRING_INDEX(domain, '.', -3)
		ELSE SUBSTRING_INDEX(domain, '.', -2) END
		AS main_domain
	FROM (
		-- Extract all (page_id, domain) tuples.
		SELECT
			el_from AS page_id,
			REGEXP_REPLACE(LOWER(CONVERT(SUBSTRING(el_to FROM 1 FOR 255) USING utf8)),  '^(?:[^:/]+:)?//((?:[-_0-9a-z]+\\.)+[a-z]+)(?:[/:?].*)?$', '\\1') AS domain
		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 SUBSTRING(el_to FROM 1 FOR 40) REGEXP '(?i)^(?:https?:|ftp:)?//(?:[-_0-9A-Za-z]+\\.)+[A-Za-z]+(?:[/:?].*)?$'
	) AS domains
) AS main_domains
GROUP BY main_domain
) AS results
ORDER BY article_count DESC, total_count DESC
LIMIT 500;

SQL

Checking query status...