Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
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
.
Toggle Highlighting
SQL
WITH base AS ( SELECT ROW_NUMBER() OVER( PARTITION BY YEAR(rev_timestamp) ORDER BY COUNT(DISTINCT rev_id) DESC ) AS rank, 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 LEFT 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 rank <= 5 #AND edits_count > 10 ORDER BY year, rank ;
By running queries you agree to the
Cloud Services Terms of Use
and you irrevocably agree to release your SQL under
CC0 License
.
Submit Query
Stop Query
All SQL code is licensed under
CC0 License
.
Checking query status...