Untitled query #88920

SQL

select
  query2.user_name                                as 'username',
  query2.user_editcount                           as 'edits',
  
  date_format(
    query2.log_timestamp, '%Y-%m-%d %H:%i:%s'
  )                                               as 'block_timestamp',
  query2.actor_name                               as 'block_admin',
  (query2.log_action = 'reblock')                 as 'reblock?',
  (query2.log_params like '%infinity%')           as 'indef?',
  (query2.log_params like '%"sitewide";b:1%')     as 'sitewide?',
  (query2.log_params like '%nocreate%')           as 'ACB?',
  (query2.log_params like '%nousertalk%')         as 'TPD?',
  (query2.log_params like '%noemail%')            as 'EMD?',
  query2.comment_text                             as 'block_comment',
  query2.log_params                               as 'block_params',
  
  if(
    query1.log_action = 'unblock',
    date_format(
      query1.log_timestamp, '%Y-%m-%d %H:%i:%s'
    ), null
  )                                               as 'unblock_timestamp',
  if(
    query1.log_action = 'unblock',
    query1.actor_name, null
  )                                               as 'unblock_admin',
  if(
    query1.log_action = 'unblock',
    query1.comment_text, null
  )                                               as 'unblock_comment',
  if(
    query1.log_action = 'unblock',
    query1.log_params, null
  )                                               as 'unblock_params'

from (
  select * from logging_userindex
  inner join actor_logging on actor_id = log_actor
  inner join comment_logging on comment_id = log_comment_id
  where log_timestamp > '2024'
  and log_type = 'block'
  and log_action regexp '^(un)?block$'
) as query1

right join (
  select * from (
    select *,
    row_number() over (
      partition by user_id order by log_timestamp desc
    ) as row_number
    from logging_userindex
    inner join actor_logging on actor_id = log_actor
    inner join comment_logging on comment_id = log_comment_id
    inner join user on user_name = replace(log_title, '_', ' ')
    left join block_target on bt_user = user_id
    left join block on bl_target = bt_id
    
    where log_timestamp > '2024'
    and bl_id is null
    and log_type = 'block'
    and log_action regexp '^(re)?block$'
    and log_params not like '%noautoblock%'
    and convert(comment_text using utf8) regexp 'spam|promo|advert'
  ) as query2_sub
  
  where query2_sub.row_number = 1
) as query2
  on query2.user_name = replace(query1.log_title, '_', ' ')
  and query1.log_timestamp > query2.log_timestamp

limit 10;
SQL

Checking query status...
