Fork of
Block history of users with 5000+ edits
by Cryptic
This query is marked as a draft
This query has been published
by Cryptic.
SQL
x
SELECT * -- this outer query is solely so we can refer to the ugly case in subquery2 by name
FROM (SELECT *,
@orig_reason := IF(log_action = 'block', comment_text, @orig_reason) AS original_reason -- how is this even legal?
FROM (SELECT log_timestamp, log_id, log_action, actor_name, REPLACE(log_title, '_', ' ') AS log_title, user_editcount, comment_text, log_params
FROM logging
JOIN user ON user_name = REPLACE(log_title, '_', ' ')
JOIN actor_logging ON log_actor = actor_id
JOIN comment_logging ON log_comment_id = comment_id
WHERE log_type = 'block'
AND user_editcount >= 5000
UNION
SELECT '99999999999999', NULL, 'currently blocked', NULL, user_name, user_editcount, NULL, NULL
FROM ipblocks_ipindex
JOIN user ON user_id = ipb_user
WHERE user_editcount >= 5000
ORDER BY log_title ASC, log_timestamp ASC
) subquery1, (SELECT @orig_reason := '') AS subquery2
) subquery3
WHERE original_reason REGEXP '(?i)(attack|harass|racis|civil|sexis|bigot|WP:(NO?)?PA|insult|slur|stereotyp|bully|gravedanc|aspersion|WP:HA|hound|following|outing|dox|off-?wiki|personali[sz])'
ORDER BY log_title ASC, log_timestamp 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.