SQL
AخA
SELECT yyyymm,
SUM(reason = 'g13') AS 'G13',
COUNT(*) AS 'total'
FROM
(
-- this irritating subquery mostly so we can ensure no deletion is classified as more than one reason,
-- without having to repeat each previous condition
SELECT CONCAT(LEFT(log_timestamp, 4), '-', SUBSTR(log_timestamp, 5, 2)) AS 'yyyymm',
CASE
WHEN log_action != 'delete' THEN 'redir'
WHEN comment_text RLIKE '(?i)(^|\\[\\[)((WP|Wikipedia):)?(CSD[-#_ ]?)?[GARFICUT][_ ]?1?[0-9]\\b'
THEN CASE WHEN comment_text RLIKE '(?i)\\bg13\\b' THEN 'g13'
ELSE 'unparsed'
END
ELSE 'unparsed'
END AS reason,
comment_text
FROM logging
LEFT JOIN comment_logging ON comment_id = log_comment_id
WHERE log_type = 'delete' AND log_action IN ('delete', 'delete_redir', 'delete_redir2')
) sq
WHERE reason = 'g13'
GROUP BY yyyymm;
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.