Untitled query #77631

SQL

USE ukwiki_p;

SELECT p.page_id, p.page_title,
  CASE
    WHEN (UniquePatrollers >= 10 AND TotalBytesChanged >= 1000) THEN 5
    WHEN (UniquePatrollers >= 6 AND TotalBytesChanged >= 800) THEN 4
    WHEN (UniquePatrollers >= 3 AND TotalBytesChanged >= 500) THEN 3
    WHEN (UniquePatrollers >= 1 AND TotalBytesChanged >= 250) THEN 2
    ELSE 1
  END AS risk_rate
FROM page p
JOIN (
  SELECT p.page_id,
         COUNT(DISTINCT ppat.actor_user) AS UniquePatrollers,
         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
  LEFT JOIN user_groups ug ON u.user_id = ug.ug_user
  LEFT JOIN actor ppat ON ug.ug_group = 'editor' AND ppat.actor_user = u.user_id
  WHERE p.page_namespace = 0
    AND r.rev_parent_id = 0
  GROUP BY p.page_id
  HAVING MIN(r.rev_len) >= (TotalBytesChanged / UniquePatrollers)  -- Minimum contribution per editor
) 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"
  )
AND p.page_is_redirect = 0
ORDER BY risk_rate DESC;
SQL

Checking query status...
