Fork of Aleksandar Trajkovski (not the footballer) blacklist candidate by Cryptic
This query is marked as a draft This query has been published by Cryptic.

SQL

x
 
SET @pat='^(?i)discord/talk.*';
SET @ns=5;
SELECT CONCAT('[[', CASE page_namespace
                      WHEN -2 THEN 'Media:'               WHEN -1 THEN 'Special:'
                      WHEN 0 THEN ''                      WHEN 1 THEN 'Talk:'
                      WHEN 2 THEN 'User:'                 WHEN 3 THEN 'User talk:'
                      WHEN 4 THEN 'Wikipedia:'            WHEN 5 THEN 'Wikipedia talk:'
                      WHEN 6 THEN ':File:'                WHEN 7 THEN 'File talk:'
                      WHEN 8 THEN 'MediaWiki:'            WHEN 9 THEN 'MediaWiki talk:'
                      WHEN 10 THEN 'Template:'            WHEN 11 THEN 'Template talk:'
                      WHEN 12 THEN 'Help:'                WHEN 13 THEN 'Help talk:'
                      WHEN 14 THEN ':Category:'           WHEN 15 THEN 'Category talk:'
                      WHEN 100 THEN 'Portal:'             WHEN 101 THEN 'Portal talk:'
                      WHEN 118 THEN 'Draft:'              WHEN 119 THEN 'Draft talk:'
                      WHEN 710 THEN 'TimedText:'          WHEN 711 THEN 'TimedText talk:'
                      WHEN 828 THEN 'Module:'             WHEN 829 THEN 'Module talk:'
                      WHEN 2300 THEN 'Gadget:'            WHEN 2301 THEN 'Gadget talk:'
                      WHEN 2302 THEN 'Gadget definition:' WHEN 2303 THEN 'Gadget definition talk:'
                      ELSE CONCAT('{{ns:', page_namespace, '}}:')
                    END, REPLACE(page_title, '_', ' '), ']]') AS 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(log_timestamp SEPARATOR ' // ') AS `deletion timestamps`,
       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 CONVERT(REPLACE(page_title, '_', ' ') USING utf8) RLIKE @pat
        AND (@ns < 0 OR page_namespace = @ns)
      UNION DISTINCT
      SELECT ar_namespace, ar_title
      FROM archive
      WHERE CONVERT(REPLACE(ar_title, '_', ' ') USING utf8) RLIKE @pat
        AND (@ns < 0 OR ar_namespace = @ns)) 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...