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

Fork of Top sites used in external links (bswiki, main namespace) v1 by MarioGom

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

SQL

SELECT 
    CONCAT_WS('.', 
        REVERSE(SUBSTRING_INDEX(REVERSE(el_to_domain_index), '.', 1)),
        REVERSE(SUBSTRING_INDEX(SUBSTRING_INDEX(REVERSE(el_to_domain_index), '.', 2), '.', -1)),
        REVERSE(SUBSTRING_INDEX(SUBSTRING_INDEX(REVERSE(el_to_domain_index), '.', 3), '.', -1)),
        REVERSE(SUBSTRING_INDEX(SUBSTRING_INDEX(REVERSE(el_to_domain_index), '.', 4), '.', -1)),
        REVERSE(SUBSTRING_INDEX(SUBSTRING_INDEX(REVERSE(el_to_domain_index), '.', 5), '.', -1))
    ) AS original_domain,
    el_to_path, 
    COUNT(*) AS counter
FROM 
    externallinks
GROUP BY 
    original_domain, 
    el_to_path
ORDER BY 
    counter DESC
LIMIT 10;

SQL

Checking query status...