avg monthy editors for projects that have graduated Incubator

SQL

WITH base_table AS (
  SELECT DISTINCT 
  	rev_id, rev_actor, rev_timestamp, rev_len, rev_parent_id,
  	page_namespace, page_id, actor_id
  FROM revision rev
  JOIN page ON rev.rev_page = page.page_id
  JOIN actor ON rev.rev_actor = actor.actor_id
  WHERE page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829) ),
  
  last_allyears AS (
    SELECT *, YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month
    FROM base_table),
  
  monthly_grouping AS (
    SELECT page_namespace, month, year,
    	COUNT(DISTINCT rev_id) AS edits_by_month,
    	COUNT(DISTINCT actor_id) AS editors_by_month
    FROM last_allyears
    GROUP BY page_namespace, month, year),
    
  avg_table AS (
    SELECT page_namespace, 
    	AVG(edits_by_month) AS avg_edits_allyears, 
    	AVG(editors_by_month) AS avg_editors_allyears
    FROM monthly_grouping
    GROUP BY page_namespace)

SELECT page_namespace, 
CAST(avg_edits_allyears AS INT) AS avg_edits_allyears, CAST(avg_editors_allyears AS INT) AS avg_editors_allyears
FROM  avg_table LIMIT 10
SQL

Checking query status...
