Top sites used in external links (bswiki, main namespace) v1

SQL

USE bswiki_p;
SELECT
	all_agg.main_domain AS main_domain,
    COUNT(*) AS total_count
FROM ( 
  SELECT
      main_domain,
      COUNT(*) AS total_count
  FROM (
      SELECT
		  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 (
          SELECT
              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
        	  -- AND
              el_from % 1000 = 0
			  -- Get only fairly well-formed URLs and exclude IPv4. Only http, https, implicit https (//) and ftp.
			  AND SUBSTRING(el_to FROM 1 FOR 40) REGEXP '(?i)^(?:https?:|ftp:)?//(?:[-_0-9a-z]+\\.)+[a-z]+(?:[/:?].*)?$'
      ) AS domains
  ) AS main_domains
GROUP BY main_domain
HAVING total_count >= 10
) AS sampled_agg JOIN (
	SELECT
		  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 (
          SELECT
              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
			  -- Get only fairly well-formed URLs and exclude IPv4. Only http, https, implicit https (//) and ftp.
			  AND SUBSTRING(el_to FROM 1 FOR 40) REGEXP '(?i)^(?:https?:|ftp:)?//(?:[-_0-9a-z]+\\.)+[a-z]+(?:[/:?].*)?$'
      ) AS domains
) AS all_agg ON sampled_agg.main_domain = all_agg.main_domain
GROUP BY all_agg.main_domain
ORDER BY total_count DESC
LIMIT 1000;
SQL

Checking query status...
