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

This query shows domains used in external links from the main namespace of Catalan 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 cawiki_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
          IF(domain REGEXP '^.*[-\\w]{4,}\\.[[:alpha:]]{2,}$',
             SUBSTRING_INDEX(domain, '.', -2),
             IF(domain REGEXP '^.*\\.(ac|edu|org|or|com|co|net|gov|gob|go)\\.[[:alpha:]]{2,}$',
                SUBSTRING_INDEX(domain, '.', -3),
                IF(domain REGEXP '.*\\.[[:alpha:]]{2}\\.(us|ca|za|it)$',
                   SUBSTRING_INDEX(domain, '.', -3),
                   SUBSTRING_INDEX(domain, '.', -2)
                  ))) AS main_domain
      FROM (
          -- Extract all (page_id, domain) tuples.
          SELECT
              el_from AS page_id,
              LOWER(CONVERT(REGEXP_REPLACE(el_to, '^(?:[^:/]+:)?//((?:[_0-9A-Za-z]+\\.)+[A-Za-z]+)(?:[/:?].*)?$', '\\1') USING utf8)) 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 main_domains
  GROUP BY main_domain
) AS results
ORDER BY article_count DESC, total_count DESC
LIMIT 500;

SQL

Checking query status...