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)
  ORDER BY rev_id DESC),
  
  edits_table AS (
  SELECT page_namespace, COUNT(DISTINCT rev_id) AS edit_count
  FROM base_table
  GROUP BY page_namespace),

  monthly_grouping AS (
    SELECT page_namespace, month, year,
    	COUNT(DISTINCT actor_id) AS editors_by_month
    FROM edits_table
    GROUP BY page_namespace, month, year)

    

SELECT *
FROM edits_table e
JOIN monthly_grouping m ON m.page_namespace = e.page_namespace
LIMIT 20

SQL

Checking query status...