Block history of users with 5000+ edits, filter by original reason

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.

(See query 76102 for the source of the list of reasons; be warned, it currently has a 1.2-million row resultset, which will lag and possibly crash your browser.)

That assignment of @orig_reason is cribbed liberally from stackoverflow.  How it's even legal, let alone why it seems to work correctly (does it?), is well beyond my experience.  I suppose it at least means the ORDER BY clause in subquery1 is no longer redundant, as it was in previous versions of this query.

For [[WP:RAQ#Blocks and unblocks]] circa 22 Aug 2023.

SQL

SELECT * -- this outer query is solely so we can refer to the ugly case in subquery2 by name
FROM (SELECT *,
             CASE
               WHEN log_action = 'block' or log_title != LAG(log_title, 1) OVER (ORDER BY log_title ASC, log_timestamp ASC)
                 THEN comment_text
               ELSE LAG(comment_text, 1) OVER (ORDER BY log_title ASC, log_timestamp ASC)
             END AS original_reason
      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
     ) subquery2
WHERE original_reason REGEXP '(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;

​x
 
SELECT * -- this outer query is solely so we can refer to the ugly case in subquery2 by nameFROM (SELECT *,             CASE               WHEN log_action = 'block' or log_title != LAG(log_title, 1) OVER (ORDER BY log_title ASC, log_timestamp ASC)                 THEN comment_text               ELSE LAG(comment_text, 1) OVER (ORDER BY log_title ASC, log_timestamp ASC)             END AS original_reason      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     ) subquery2WHERE original_reason REGEXP '(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.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...