SQL
AخA
SELECT yyyymm,
SUM(reason = 'redir') AS 'redirect overwrites',
SUM(reason = 'a7+g11') AS 'A7 and G11',
SUM(reason = 'a7') AS 'A7',
SUM(reason = 'g11') AS 'G11',
SUM(reason = 'g13') AS 'G13',
SUM(reason = 'speedy') AS 'Other speedy',
SUM(reason = 'afd') AS 'AFD',
SUM(reason = 'cfd') AS 'CFD',
SUM(reason = 'rfd') AS 'RFD',
SUM(reason = 'tfd') AS 'TFD',
SUM(reason = 'ffd') AS 'FFD',
SUM(reason = 'mfd') AS 'MFD',
SUM(reason = 'prod') AS 'prod',
SUM(reason = 'unparsed') AS 'unparsed',
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)\\ba7\\b' AND comment_text RLIKE '(?i)\\bg11\\b' THEN 'a7+g11'
WHEN comment_text RLIKE '(?i)\\ba7\\b' THEN 'a7'
WHEN comment_text RLIKE '(?i)\\bg11\\b' THEN 'g11'
WHEN comment_text RLIKE '(?i)\\bg13\\b' THEN 'g13'
ELSE 'speedy'
END
WHEN comment_text RLIKE '(?i)(\\[\\[(WP|Wikipedia):(Votes|Articles)[_ ]for[_ ]deletion|\\b[av]fd\\b)' THEN 'afd'
WHEN comment_text RLIKE '(?i)(\\[\\[(WP|Wikipedia):Categories[_ ]for[_ ](deletion|discussion)|\\bcfd\\b)' THEN 'cfd'
WHEN comment_text RLIKE '(?i)(\\[\\[(WP|Wikipedia):Redirects[_ ]for[_ ](deletion|discussion)|\\brfd\\b)' THEN 'rfd'
WHEN comment_text RLIKE '(?i)(\\[\\[(WP|Wikipedia):Templates[_ ]for[_ ](deletion|discussion)|\\btfd\\b)' THEN 'tfd'
WHEN comment_text RLIKE '(?i)(\\[\\[(WP|Wikipedia):(Files|Images)[_ ]for[_ ](deletion|discussion)|\\b[fi]fd\\b)' THEN 'ffd'
WHEN comment_text RLIKE '(?i)(\\[\\[(WP|Wikipedia):Miscellany[_ ]for[_ ]deletion|\\bmfd\\b)' THEN 'mfd'
WHEN comment_text RLIKE '(?i)(\\bprod(ded)?\\b|proposed deletion)' THEN 'prod'
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
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.