Untitled query #87369

SQL

WITH base AS (
    SELECT 
        ROW_NUMBER() OVER(
            PARTITION BY YEAR(rev_timestamp)
            ORDER BY COUNT(DISTINCT rev_id) DESC
        ) AS rank,
        YEAR(rev_timestamp) AS year,
        user_name,
        COUNT(DISTINCT rev_id) AS edits_count,  
        CASE
            WHEN up_property = 'gender' THEN up_value
            ELSE 'unknown'
        END AS gender  
    FROM
        revision
    JOIN
        actor ON rev_actor = actor_id
    JOIN
        user_groups ON actor_user = ug_user
    LEFT JOIN 
        user_properties ON ug_user = up_user  
    WHERE
        actor_name IS NOT NULL
        AND user_name NOT LIKE '%Bot%'
        AND user_name NOT LIKE 'MediaWiki message delivery'
        AND user_name NOT LIKE 'New user message'
        AND user_name NOT LIKE '%AWB%'
        AND YEAR(rev_timestamp) BETWEEN 2019 AND 2024  
    GROUP BY
        year,
        user_name
)
SELECT
    *
FROM
    base
WHERE 
    rank <= 5
    AND edits_count > 10
ORDER BY 
    year, 
    rank;
SQL

Checking query status...
