Untitled query #86638

SQL

WITH base AS (
  SELECT 
    YEAR(rev_timestamp) AS year,
    actor_name AS user_name,   
    COUNT(rev_id) AS edits_count,  
    MAX(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 AND up_property = 'gender'  
  WHERE 
    rev_timestamp BETWEEN '2019-01-01' AND '2024-12-31'  
    AND actor_name IS NOT NULL   
    AND actor_name NOT LIKE '%Bot%'
    AND actor_name NOT LIKE '%bot%'
    AND actor_name NOT LIKE 'MediaWiki message delivery'  
    AND actor_name NOT LIKE 'New user message'  
  GROUP BY 
    actor_name, YEAR(rev_timestamp)  
  HAVING 
    COUNT(rev_id) > 5   
)

SELECT *
FROM base
ORDER BY edits_count DESC;
SQL

Checking query status...
