Untitled query #77631

SQL

USE ukwiki_p;

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 a.actor_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
  LEFT JOIN user u ON a.actor_user = u.user_id
  WHERE p.page_namespace = 0
    AND r.rev_parent_id = 0
    AND NOT EXISTS (
      SELECT 1
      FROM user_groups ug
      WHERE ug.ug_user = a.actor_user
      AND ug.ug_group = 'patroller'
    )
  GROUP BY p.page_id
) AS Subquery ON p.page_id = Subquery.page_id
WHERE p.page_id IN (
    SELECT p.page_id
    FROM page p
    JOIN revision r ON p.page_id = r.rev_page
    JOIN actor a ON r.rev_actor = a.actor_id
    LEFT JOIN user u ON a.actor_user = u.user_id
    WHERE p.page_namespace = 0
    AND r.rev_parent_id = 0
    AND u.user_name = "Haida"
  );
SQL

Checking query status...
