SQL
AخA
USE dewiki_p; SET @language = 'de';
WITH externallinksforhumans AS (
SELECT
el_from,
# https://quarry.wmcloud.org/query/77235 :
REGEXP_REPLACE(el_to_domain_index,
'^(.*?://)(?:([^.]+)\\.)([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)?([^.]+\\.)$',
'\\1\\9\\8\\7\\6\\5\\4\\3\\2/') AS rooturl,
el_to_path
FROM externallinks)
SELECT
CONCAT('https://', @language , '.wikipedia.org/wiki/' ,page_title, '?action=edit&veswitched=1#:~:text=', rooturl) AS page_edit_link,
# try to highlight homepage URL in source wikitext
rooturl,
url,
SUM(IF( url=rooturl, 1, 0) ) AS rootlinks,
SUM(1) AS alllinks
FROM (
SELECT
page_title,
CONCAT(rooturl,SUBSTRING(el_to_path,1)) AS url,
rooturl
FROM externallinksforhumans, page
WHERE el_from = page_id
AND page_namespace = 0
# restrict query to a subset of articles for performance reasons:
AND LEFT(page_title, 1) = 'N'
) AS pagelinks
GROUP BY page_title, rooturl
LIMIT 200
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.