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