Untitled query #86639

SQL

WITH base AS (
  SELECT 
    actor_name AS user_name,   
    COUNT(DISTINCT rev_id) AS edits_count,  -- Ensure distinct edits are counted
    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%'  -- Exclude bot names
    AND actor_name NOT LIKE '%bot%'  -- Case insensitive bot exclusion
    AND actor_name NOT LIKE 'MediaWiki message delivery'  
    AND actor_name NOT LIKE 'New user message'  
    AND actor_name NOT LIKE 'Satdeepbot'  -- Exclude Satdeepbot
  GROUP BY 
    actor_name  
  HAVING 
    COUNT(DISTINCT rev_id) > 5   -- Only include users with more than 5 distinct edits
)

SELECT *
FROM base
ORDER BY edits_count DESC  -- Order by edit count in descending order to get top editors
LIMIT 20;  -- Limit the results to the top 20 editors
SQL

Checking query status...
