Untitled query #88917

SQL

select
  user1.user_name as user_name,
  user1.user_editcount as user_editcount,
  
  query2.log_timestamp as log1_timestamp,
  query2.actor_name as log1_admin,
  query2.log_action as log1_action,
  query2.log_params as log1_params,
  query2.comment_text as log1_comment,
  
  logging1.log_timestamp as log2_timestamp,
  actor1.actor_name as log2_admin,
  logging1.log_action as log2_action,
  comment1.comment_text as log2_comment,
  logging1.log_params as log2_params,
  
  row_number() over (partition by user1.user_id order by logging1.log_timestamp asc) as row_number

from logging_logindex 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 as user1
  on user1.user_name = replace(logging1.log_title, '_', ' ')

-- use a right join, so we (hopefully) get all of the original block events, even if there wasn't
  -- a specific unblock for it (ie., if the block auto-expired)
right join (
  select user_id, log_timestamp, actor_name, comment_text, log_action, log_params
  
  from ( 
    select
      *,
      row_number() over (partition by user_id order by logging2.log_timestamp desc) as row_number
    
    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
    inner join user as user2
      on user2.user_name = replace(logging2.log_title, '_', ' ')
    left join block_target as block_target2
      on block_target2.bt_user = user2.user_id
    left join block as block2
      on block2.bl_target = block_target2.bt_id
    
    where logging2.log_timestamp > '2024'
    and block2.bl_id is null
    and logging2.log_type = 'block'
    and logging2.log_action regexp '^(re)?block$'
    and logging2.log_params not like '%noautoblock%'
    and convert(comment2.comment_text using utf8) regexp 'spam|promo|advert'
  ) as query1
  
  where query1.row_number = 1
  
) as query2
  on query2.user_id = user1.user_id
  and logging1.log_timestamp > query2.log_timestamp
  

where logging1.log_timestamp > '2024'
and logging1.log_type = 'block'

-- searching for both unblocks *and* new blocks:
-- if there is no unblock action (ie., because the block was time-limited), then
-- searching for unblock actions alone could potentially return an unblock summary
-- from a potential *different future block* (which could be for an unrelated reason).
-- therefore, if there's a `block` event before an `unblock` event, we know to filter
-- that unblock event out.
and logging1.log_action regexp '^(un)?block$';

-- limit 1;



-- order by logging1.log_timestamp desc
SQL

Checking query status...
