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.