Untitled query #77631

SQL

CREATE TEMPORARY TABLE temp_articles AS
SELECT p.page_id AS article_id,
       COUNT(DISTINCT r.rev_user) AS unique_editors,
       SUM(r.rev_len) AS total_bytes_changed
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 u.user_name = "Haida"
  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;

-- Update the "risk_rate" parameter for each article based on the criteria.
UPDATE page AS p
JOIN temp_articles AS ta ON p.page_id = ta.article_id
SET p.page_content_model = "wikitext", p.page_content = 
CASE
  WHEN ta.unique_editors >= 10 AND ta.total_bytes_changed >= 10000 THEN 
    REPLACE(p.page_content, '}}', '|risk_rate=5}}')
  WHEN ta.unique_editors >= 6 AND ta.total_bytes_changed >= 6000 THEN 
    REPLACE(p.page_content, '}}', '|risk_rate=4}}')
  WHEN ta.unique_editors >= 3 AND ta.total_bytes_changed >= 3000 THEN 
    REPLACE(p.page_content, '}}', '|risk_rate=3}}')
  WHEN ta.unique_editors >= 1 AND ta.total_bytes_changed >= 500 THEN 
    REPLACE(p.page_content, '}}', '|risk_rate=2}}')
  ELSE 
    REPLACE(p.page_content, '}}', '|risk_rate=1}}')
END;

-- Drop the temporary table.
DROP TEMPORARY TABLE temp_articles;
SQL

Checking query status...
