Untitled query #87371

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 ON actor_user = user_id
  	LEFT JOIN 
    	user_properties ON user_id = up_user 
    JOIN 
        user_groups ON user_id = ug_user
  	WHERE
    	actor_name IS NOT NULL
    	AND user_name NOT IN ('%Bot%','MediaWiki message delivery','New user message','%bot%','InternetArchiveBot')
    	AND YEAR(rev_timestamp) BETWEEN 2019 AND 2024  
  	GROUP BY
    	user_name, 
  		year
  ORDER BY
    edits_count DESC,
    year
)

SELECT
	*
FROM
	base
WHERE 
	rank <= 5
    AND edits_count > 10
ORDER BY 
	year, 
    rank
;
SQL

Checking query status...
