USE skwiki_p;

​

SELECT 

    log_title,

    COUNT(log_title) AS number_of_blocks

FROM

    logging_userindex

WHERE

    log_namespace=2 AND

    log_type='block' AND

    log_action='block' AND -- but not 'reblock' or 'unblock'

    log_title NOT REGEXP '^[0-9]{1,3}(\.[0-9]{1,3}){0,3}(\/[0-9]{1,2})?$' AND -- no IPv4 addresses (and ranges)

    log_title NOT REGEXP '^[0-9A-Fa-f]{1,4}(:([0-9A-Fa-f]{1,4})?){1,7}(\/[0-9]{1,2})?$' -- no IPv6 addresses (and ranges)

GROUP BY

    log_title

HAVING

    COUNT(log_title) > 1

ORDER BY

    number_of_blocks DESC, log_title;