Fork of Deji Olatunji blacklisting by Cryptic
This query is marked as a draft This query has been published by Cryptic.

SQL

x
 
SET @pat='(?i)(honorary.*citizen)';
SELECT page_title,
       EXISTS (SELECT 1
               FROM page p2
               WHERE p2.page_namespace = sq.page_namespace
                 AND p2.page_title = sq.page_title) AS existing,
       COUNT(log_namespace) AS `times deleted`,
       GROUP_CONCAT(comment_text SEPARATOR ' // ') AS `deletion comments`,
       (SELECT GROUP_CONCAT(comment_text SEPARATOR ' // ')
        FROM logging_logindex
        JOIN comment_logging ON comment_id = log_comment_id
        WHERE log_namespace = page_namespace
          AND log_title = page_title
          AND log_type = 'protect'
          AND log_action = 'protect'
          AND log_params LIKE '%create=%') AS `salt comments`
FROM (SELECT page_namespace, page_title
      FROM page
      WHERE REPLACE(page_title, '_', ' ') RLIKE @pat
        AND page_namespace = 14
      UNION DISTINCT
      SELECT ar_namespace, ar_title
      FROM archive
      WHERE REPLACE(ar_title, '_', ' ') RLIKE @pat
        AND ar_namespace = 14) sq
LEFT JOIN logging_logindex
       ON log_namespace = page_namespace
      AND log_title = page_title
      AND log_action = 'delete'
      AND log_type = 'delete'
LEFT JOIN comment_logging ON comment_id = log_comment_id
GROUP BY page_namespace, page_title
ORDER BY `existing` DESC, page_namespace ASC, page_title ASC;
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.

Checking query status...