Untitled query #69332

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
  FROM revision rev
  JOIN page ON rev.rev_page = page.page_id
  JOIN actor ON rev.rev_actor = actor.actor_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)
  HAVING prefix <> ''
  ORDER BY rev_id DESC),
  
  
  since_2006 AS (
    SELECT *, YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month
    FROM base_table
    WHERE YEAR(rev_timestamp) >= 2006),
  
  monthly_grouping AS (
    SELECT prefix, month, year,
    	COUNT(DISTINCT rev_id) AS edits_by_month,
    	COUNT(DISTINCT actor_id) AS editors_by_month
    FROM since_2006
    GROUP BY prefix, month, year)
    
 

SELECT *
FROM monthly_grouping
ORDER BY year, month, prefix
LIMIT 100
SQL

Checking query status...
