Compare each top editor's total edits against the overall average

calculate the mean of all editors' edits. Then, for each top editor, subtract the overall average from their individual total to assess how much their contribution deviates from the average.

SQL

WITH total_edits AS (
  SELECT 
    rev_actor,  -- Assuming rev_actor is the user ID or identifier for the editor
    COUNT(rev_id) AS edit_count
  FROM revision
  WHERE revision_timestamp >= DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH)  -- Filter for the last 3 months
  GROUP BY rev_actor
),
overall_average AS (
  SELECT 
    ROUND(AVG(edit_count), 0) AS avg_edits
  FROM total_edits
)

SELECT 
  te.rev_actor,
  te.edit_count,
  ROUND(((te.edit_count - oa.avg_edits) / oa.avg_edits) * 100, 2) AS percentage_above_average
FROM total_edits te, overall_average oa
WHERE te.edit_count > oa.avg_edits  -- Optional: Only show editors who are above average
ORDER BY te.edit_count DESC
LIMIT 5;  -- Limit to top 5 editors

SQL

Checking query status...