Untitled query #38109

This query is marked as a draft This query has been published by RyanSteinberg.

SQL

SET @domain_regex = '.+(\\.[^\\.]+)$';
SET @hostname_regex = '^([^:/?#]+:)?(\/+([^/?#:]*))?([^\?#]*)\?.*';
SELECT
REGEXP_REPLACE(LOWER(REGEXP_REPLACE(el_to, @hostname_regex, '\\3')), @domain_regex, '\\1') as tld,
  count(distinct el_from, el_to) as num_links
FROM
  externallinks
WHERE
  el_from IN (
    SELECT
      DISTINCT page_id
    FROM
      page
    WHERE
      page_namespace = 0
  )
GROUP BY tld
ORDER BY num_links desc
LIMIT 1000;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...