Percent more than Average

SQL

-- Create a view to calculate total edits per editor in the rolling 3-month period
CREATE VIEW EditorEdits AS
SELECT 
    actor.actor_id,
    actor.actor_name,
    COUNT(DISTINCT revision.rev_id) AS total_edits
FROM revision
JOIN actor ON revision.rev_actor = actor.actor_id
WHERE revision.rev_timestamp >= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH), '%Y%m01000000')  -- Rolling 3-month window
GROUP BY actor.actor_id, actor.actor_name;


-- Select top 5 editors and compare their total edits to the overall average using the view
SELECT 
    editor.actor_name,  -- Editor's name
    editor.total_edits,  -- Total number of edits per editor
    (editor.total_edits / (SELECT AVG(total_edits) FROM EditorEdits)) * 100 AS percent_more_than_average  -- Percentage more than average
FROM EditorEdits AS editor
ORDER BY editor.total_edits DESC  -- Order by the number of edits (most first)
LIMIT 5;  -- Limit to top 5 editors
SQL

Checking query status...
