Untitled query #88871

This query is marked as a draft This query has been published by A smart kitten.

aaaaaaaaaaaaa

SQL

select
  -- user info
  query3.user_name as username,
  query3.user_editcount as edits,
  
  -- [re]block
  query3.log1_timestamp as block_timestamp,
  query3.log1_admin as block_admin,
  (query3.log1_action = 'reblock') as 'reblock?',
  (query3.log1_params like '%infinity%') as 'indef?',
  (query3.log1_params like '%"sitewide";b:1%') as 'sitewide?',
  (query3.log1_params like '%nocreate%') as 'ACB?',
  (query3.log1_params like '%nousertalk%') as 'TPD?',
  (query3.log1_params like '%noemail%') as 'EMD?',
  query3.log1_comment as block1_comment,
  query3.log1_params as block1_params,  -- temp
  
  -- unblock
  -- (use some terrible conditional statements to display NULL values if there is no unblock, just a reblock)
  if(query3.log2_action = 'unblock', query3.log2_timestamp, null) as unblock_timestamp,
  if(query3.log2_action = 'unblock', query3.log2_admin, null) as unblock_admin,
  if(query3.log2_action = 'unblock', query3.log2_comment, null) as unblock_comment,
  if(query3.log2_action = 'unblock', query3.log2_params, null) as unblock_params,  -- temp
  
  query3.row_number  -- temp

from (
  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 user2.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 logging1.log_timestamp > '2024'
    and logging1.log_timestamp > query2.log_timestamp
    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$'
    and query2.user_id = user1.user_id
  
) as query3

where query3.row_number = 1;

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...