Untitled query #86638

SQL

WITH base AS (
  SELECT 
    YEAR(rev_timestamp) AS year,
    actor_name AS user_name,   -- Actor name is used as the username
    COUNT(rev_id) AS edits_count,  -- Counting edits made by the user
    MAX(CASE WHEN up_property = 'gender' THEN up_value ELSE 'unknown' END) AS gender  -- Extract gender
  FROM 
    revision
  JOIN 
    actor ON rev_actor = actor_id  -- Link to actor to get the user's name
  JOIN 
    user ON actor_user = user_id  -- Link actor to user
  LEFT JOIN 
    user_properties ON user_id = up_user AND up_property = 'gender'  -- Join to get gender information
  WHERE 
    rev_timestamp BETWEEN '2019-01-01' AND '2024-12-31'  -- Date range filter
    AND actor_name IS NOT NULL   -- Ensure actor names are valid
    AND actor_name NOT LIKE '%Bot%'  -- Exclude bots
    AND actor_name NOT LIKE 'MediaWiki message delivery'  -- Exclude system messages
  GROUP BY 
    actor_name, YEAR(rev_timestamp)  -- Group by user and year to capture edits count
  HAVING 
    COUNT(rev_id) > 5   -- Filter users with more than 5 edits
)

SELECT *
FROM base
ORDER BY edits_count DESC;  -- Order by edit count to match Wikiscan
SQL

Checking query status...
