Untitled query #75740

SQL

-- Select distinct actor names and give them an alias "ll_actor_name"
SELECT 
  DISTINCT(actor_name) AS ll_actor_name,
  
  -- Select user groups
  ug_group AS user_groups,
  
  -- Calculate the latest edit timestamp and call it "last_edit_date"
  MAX(rui.rev_timestamp) AS last_edit_date
  
FROM 
  -- Join actor_revision table with user_groups based on actor_user
  actor_revision ar
  JOIN user_groups ON ar.actor_user = ug_user 
  
  -- Join actor_revision table with user based on actor_user
  JOIN user ON ar.actor_user = user.user_id 
  
  -- Left join ipblocks table based on actor_user
  LEFT JOIN ipblocks ON ar.actor_user = ipb_user 
  
  -- Left join revision_userindex based on actor_id
  LEFT JOIN revision_userindex rui ON ar.actor_id = rui.rev_actor 
  
WHERE 
  -- Select specific user groups
  ug_group IN ('editor', 'autoreview', 'uploader')
  
  -- Filter out records where ipb_user is NULL
  AND ipb_user IS NULL 
  
  -- Filter out records with NULL rev_timestamp or within the last year
  AND (rui.rev_timestamp IS NULL OR rui.rev_timestamp <= DATE_SUB(NOW(), INTERVAL 1 YEAR))
  
-- Group results by actor_name and user_groups
GROUP BY 
  actor_name, 
  user_groups;
SQL

Checking query status...
