SQL
x
select * /*
user.user_name as 'username',
user.user_editcount as 'edits',
date_format(
logging1.log_timestamp, '%Y-%m-%d %H:%i:%s'
) as 'block_timestamp',
actor1.actor_name as 'block_admin',
(logging1.log_action = 'reblock') as 'reblock?',
(logging1.log_params like '%infinity%') as 'indef?',
(logging1.log_params like '%"sitewide";b:1%') as 'sitewide?',
(logging1.log_params like '%nocreate%') as 'ACB?',
(logging1.log_params like '%nousertalk%') as 'TPD?',
(logging1.log_params like '%noemail%') as 'EMD?',
comment1.comment_text as 'block_comment',
logging1.log_params as 'block_params',
if(
logging_subquery.log_action = 'unblock',
date_format(
logging_subquery.log_timestamp,
'%Y-%m-%d %H:%i:%s'
), null
) as 'unblock_timestamp',
if(
logging_subquery.log_action = 'unblock',
logging_subquery.actor_name, null
) as 'unblock_admin',
if(
logging_subquery.log_action = 'unblock',
logging_subquery.comment_text, null
) as 'unblock_comment',
if(
logging_subquery.log_action = 'unblock',
logging_subquery.log_params, null
) as 'unblock_params'
*/
from (
select
*,
row_number() over (partition by user_id order by logging1.log_timestamp desc) as row_number
-- logging1 fetches the most recent [re]block event
-- that matches the given filters
from logging_userindex as logging1
inner join actor_logging as actor1
on actor1.actor_id = logging1.log_actor
inner join comment_logging as comment1
on comment1.comment_id = logging1.log_comment_id
inner join user
on user.user_name = replace(logging1.log_title, '_', ' ')
-- left join on the block table
-- so we can filter out users that are currently blocked
left join block_target
on block_target.bt_user = user.user_id
left join block
on block.bl_target = block_target.bt_id
-- logging2 fetches the [un]block event that comes soonest
-- after the event fetched by logging1
left join (
select
logging2.log_timestamp, logging2.log_title, logging2.log_action,
logging2.log_params, actor2.actor_name, comment2.comment_text
from logging_userindex as logging2
inner join actor_logging as actor2
on actor2.actor_id = logging2.log_actor
inner join comment_logging as comment2
on comment2.comment_id = logging2.log_comment_id
where logging2.log_timestamp > '2024'
and logging2.log_type = 'block'
and logging2.log_action regexp '^(un)?block'
) as logging_subquery
on user.user_name = replace(logging_subquery.log_title, '_', ' ')
and logging_subquery.log_timestamp > logging1.log_timestamp
-- filtering the [re]block event
where logging1.log_timestamp > '2024'
and block.bl_id is null
and logging1.log_type = 'block'
and logging1.log_action regexp '^(re)?block$'
and logging1.log_params not like '%noautoblock%'
and convert(comment1.comment_text using utf8) regexp 'spam|promo|advert'
) as subquery
where subquery.row_number = 1
limit 3;
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.