Testing - Quarry

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
          IF(domain REGEXP '^.*[-\\w]{4,}\\.[[:alpha:]]{2,}$',
             SUBSTRING_INDEX(domain, '.', -2),
             IF(domain REGEXP '^.*\\.(ac|edu|org|or|com|co|net|gov|go)\\.[[:alpha:]]{2,}$',
                SUBSTRING_INDEX(domain, '.', -3),
                IF(domain REGEXP '.*\\.[[:alpha:]]{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,
              REGEXP_REPLACE(el_to, '^(?:[^:/]+:)?//((?:[_0-9A-Za-z]+\\.)+[_0-9A-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]+\\.)+[_0-9A-Za-z]+(?:[/:?].*)?$'
      ) AS domains
  ) AS main_domains
  GROUP BY main_domain
) AS results
WHERE article_count >= 500
ORDER BY article_count DESC, total_count DESC;
SQL

Checking query status...
