Untitled query #77631

SQL

SELECT p.page_id, p.page_title,
  CASE
    WHEN (UniqueEditors >= 10 AND TotalBytesChanged >= 10000) THEN 5
    WHEN (UniqueEditors >= 6 AND TotalBytesChanged >= 6000) THEN 4
    WHEN (UniqueEditors >= 3 AND TotalBytesChanged >= 3000) THEN 3
    WHEN (UniqueEditors >= 1 AND TotalBytesChanged >= 500) THEN 2
    ELSE 1
  END AS risk_rate
FROM page p
JOIN (
  SELECT p.page_id,
         COUNT(DISTINCT r.rev_user) AS UniqueEditors,
         SUM(r.rev_len) AS TotalBytesChanged
  FROM page p
  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
  LEFT JOIN user_groups ug ON u.user_id = ug.ug_user
  WHERE p.page_namespace = 0
    AND r.rev_parent_id = 0
    AND NOT EXISTS (
      SELECT 1
      FROM user_groups ug2
      WHERE ug2.ug_user = u.user_id
      AND ug2.ug_group = 'patroller'
    )
  GROUP BY p.page_id
) AS Subquery ON p.page_id = Subquery.page_id
WHERE u.user_name = "Haida";
SQL

Checking query status...
