Top 20 active users of Punjabi Wikipedia over the last 5 years

Fork of Top 20 active users of Punjabi Wikipedia over the last 5 years by Tulspal

This query is marked as a draft This query has been published by KCVelaga.

SQL

WITH base AS (
  SELECT 
    ROW_NUMBER() OVER(
      PARTITION BY YEAR(rev_timestamp)
      ORDER BY COUNT(DISTINCT rev_id) DESC
  ) AS row_num,
    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
  JOIN 
    user_properties ON user_id = up_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 
  row_num <= 5
  #AND edits_count > 10
ORDER BY year, row_num;

SQL

Checking query status...