To AfC reviewers

SQL

SELECT 
    RANK() OVER (ORDER BY COUNT(rev_id) DESC) AS Rank,
    COUNT(rev_id) AS Reviews,
    a.actor_name AS Reviewer,
    SUM(c.comment_text LIKE "%Publishing accepted%") AS Accept,
    SUM(c.comment_text LIKE "Declining submission:%") AS Decline,
    SUM(c.comment_text LIKE "Commenting on submission%") AS Comment,
    SUM(c.comment_text LIKE "Rejecting submission:%") AS Reject,
    CONCAT(ROUND(SUM(c.comment_text LIKE "%Publishing accepted%") * 100 / COUNT(rev_id), 1), "%") AS "Accept %",
    CONCAT(ROUND(SUM(c.comment_text LIKE "Declining submission:%") * 100 / COUNT(rev_id), 1), "%") AS "Decline %",
    CONCAT(ROUND(SUM(c.comment_text LIKE "Commenting on submission%") * 100 / COUNT(rev_id), 1), "%") AS "Comment %",
    CONCAT(ROUND(SUM(c.comment_text LIKE "Rejecting submission:%") * 100 / COUNT(rev_id), 1), "%") AS "Reject %"
FROM (
    SELECT rev_id, rev_actor, rev_comment_id, rev_timestamp
    FROM revision
    WHERE rev_timestamp >= NOW() - INTERVAL 1 YEAR
    UNION ALL
    SELECT ar_id AS rev_id, ar_actor AS rev_actor, ar_comment_id AS rev_comment_id, ar_timestamp AS rev_timestamp
    FROM archive
    WHERE ar_timestamp >= NOW() - INTERVAL 1 YEAR
) AS combined
LEFT JOIN actor a ON combined.rev_actor = a.actor_id
LEFT JOIN comment c ON combined.rev_comment_id = c.comment_id
WHERE 
    (c.comment_text LIKE "Declining submission:%" 
         OR c.comment_text LIKE "Rejecting submission:%"
         OR c.comment_text LIKE "Commenting on submission%"
         OR c.comment_text LIKE "%Publishing accepted%")
GROUP BY 
    combined.rev_actor
ORDER BY 
    Reviews DESC
LIMIT 100;
SQL

Checking query status...
