Incubator, avg monthly editors

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,
          COUNT(DISTINCT rev_id) AS edit_count,
          COUNT(DISTINCT actor_id) AS actor_count,
          SUM(CASE WHEN byte_diff < 0 THEN byte_diff ELSE 0 END) AS bytes_removed_30D,
          SUM(CASE WHEN byte_diff >= 0 THEN byte_diff ELSE 0 END) AS bytes_added_30D,
          COUNT(DISTINCT page_id) AS pages_count
      FROM
          base_table
      GROUP BY 
      	prefix),
      
	month_year AS (
      SELECT
      		*,
      		YEAR(rev_timestamp) AS year,
      		MONTH(rev_timestamp) AS month
      FROM 
      		base_table),
      
      monthly_grouping AS (
    	SELECT 
        	prefix, 
        	month, 
    		COUNT(DISTINCT actor_id) AS editors_monthly
    	FROM 
        	month_year
    	GROUP BY 
        	prefix, month),  
  
  	avg_monthly AS (
    	SELECT 
    		prefix, 
      		ROUND(AVG(editors_monthly)) AS avg_editors_monthly
    	FROM 
    		monthly_grouping
    	GROUP BY 
    		prefix)

SELECT 
    av.avg_editors_monthly
FROM
	primary_metrics pm
JOIN
	avg_monthly av
    ON pm.prefix = av.prefix
ORDER BY avg_editors_monthly DESC
SQL

Checking query status...
