Top Active editors of pawiki 2023

SQL

WITH base AS (
  SELECT 
    YEAR(rev_timestamp) AS year,
    user_name,
    COUNT(DISTINCT rev_id) AS edits_count,  -- Ensuring distinct revisions
    MAX(CASE
      WHEN up_property = 'gender' THEN up_value
      ELSE 'unknown'
    END) AS gender  -- Simplifying gender case condition
  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  -- Using LEFT JOIN in case some users don't have properties
  WHERE
    actor_name IS NOT NULL
    AND YEAR(rev_timestamp) = 2023  -- Exact match for 2023, rather than LIKE
    AND user_name NOT LIKE '%Bot%'
    AND user_name NOT LIKE 'MediaWiki message delivery'
  GROUP BY
    user_name, YEAR(rev_timestamp)  -- Grouping by year and user to avoid duplication
  ORDER BY
    edits_count DESC
)

SELECT
  *
FROM
  base
WHERE
  edits_count > 5;
SQL

Checking query status...
