Untitled query #77631

SQL

WITH patrollers_count AS (
  SELECT p.page_id,
         COUNT(DISTINCT u.user_id) AS unique_patrollers
  FROM page p
  JOIN revision r ON p.page_id = r.rev_page
  LEFT JOIN actor a ON r.rev_actor = a.actor_id
  LEFT JOIN user u ON a.actor_user = u.user_id
  LEFT JOIN user_groups ug ON u.user_id = ug.ug_user
  WHERE p.page_namespace = 0
    AND r.rev_parent_id = 0
    AND ug.ug_group = 'editor'
  GROUP BY p.page_id
  HAVING MIN(r.rev_len) >= 500
)

SELECT p.page_id, p.page_title, pc.unique_patrollers
FROM page p
JOIN patrollers_count pc ON p.page_id = pc.page_id
JOIN revision r ON p.page_id = r.rev_page
JOIN actor a ON r.rev_actor = a.actor_id
JOIN user u ON a.actor_user = u.user_id AND u.user_name = "Haida"
WHERE p.page_namespace = 0
  AND r.rev_parent_id = 0
  AND p.page_is_redirect = 0;
  
ORDER BY pc.unique_patrollers DESC;
SQL

Checking query status...
