Untitled query #87178

SQL

WITH rolling_edits AS (
    -- Get the total number of edits by each user in the rolling 3-month period
    SELECT 
        r.rev_user,
        COUNT(r.rev_id) AS total_edits
    FROM 
        revision r
    WHERE 
        r.rev_timestamp BETWEEN 
            DATE_FORMAT(NOW() - INTERVAL 3 MONTH, '%Y%m01000000') AND 
            DATE_FORMAT(NOW(), '%Y%m%d235959')
    GROUP BY 
        r.rev_user
),
average_edits AS (
    -- Calculate the overall average number of edits per user in the rolling 3-month period
    SELECT 
        AVG(total_edits) AS avg_edits
    FROM 
        rolling_edits
)
SELECT 
    u.user_name, 
    re.total_edits, 
    ae.avg_edits,
    ((re.total_edits - ae.avg_edits) / ae.avg_edits) * 100 AS percentage_above_avg
FROM 
    rolling_edits re
JOIN 
    user u ON re.rev_user = u.user_id
CROSS JOIN 
    average_edits ae
ORDER BY 
    re.total_edits DESC
LIMIT 5;
SQL

Checking query status...
