Toggle navigation
Home
New Query
Recent Queries
Discuss
Database tables
Database names
MediaWiki
Wikibase
Replicas browser and optimizer
Login
History
Fork
This query is marked as a draft
This query has been published
by
CMyrick-WMF
.
Toggle Highlighting
SQL
WITH base_table AS ( SELECT DISTINCT rev_id, rev_actor, rev_timestamp, rev_len, rev_parent_id, page_namespace, page_id, actor_id, REGEXP_SUBSTR(page_title, 'W[a-z]/[a-z]+') AS prefix, rc_new_len - rc_old_len AS byte_diff, ug_group FROM revision rev JOIN page p ON rev.rev_page = p.page_id JOIN actor a ON rev.rev_actor = a.actor_id JOIN user u ON a.actor_user = u.user_id LEFT JOIN user_groups ug ON ug.ug_user = u.user_id LEFT OUTER JOIN recentchanges rc ON rev.rev_id = rc.rc_this_oldid WHERE page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829) AND user_name IS NOT NULL AND (ug_group NOT IN ("bot") OR ug_group IS NULL) HAVING prefix <> ''), primary_metrics AS ( SELECT prefix, MIN(rev_timestamp) AS min_edit, MAX(rev_timestamp) AS max_edit, FROM base_table GROUP BY prefix), years_months AS ( SELECT prefix, YEAR(min_edit) AS year_min_edit, MONTH(min_edit) AS month_min_edit, YEAR(max_edit) AS year_max_edit, MONTH(max_edit) AS month_max_edit FROM primary_metrics ) SELECT * FROM primary_metrics JOIN avg_3M av ON primary_metrics.prefix = years_months.prefix
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...