Incubator: first and last edits per project

SQL

WITH 
	base_table AS (
      SELECT DISTINCT 
        rev_id, 
        rev_timestamp, 
        rev_len, 
        rev_page,
        rev_parent_id,
        page_namespace, 
        page_id,
        REGEXP_SUBSTR(page_title, 'W[a-z]/[a-z]+') AS prefix,
      FROM 
        revision rev 
      JOIN 
        page p
        ON rev.rev_page = p.page_id
      WHERE 
        -- page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829) AND
      HAVING 
        prefix <> ''
    ),
  
	primary_metrics AS (
      SELECT
          prefix,
      	  MIN(rev_timestamp) AS min_rev,
     	  MAX(rev_timestamp) AS max_rev
      FROM
          base_table 
      GROUP BY 
          prefix
    ),
          
     years_months AS (
      SELECT
          prefix,
      	  YEAR(min_rev) AS year_min_rev,
      	  MONTH(min_rev) AS month_min_rev,
      	  YEAR(max_rev) AS year_max_rev,
          MONTH(max_rev) AS month_max_rev
      FROM
      	   primary_metrics
     )
           

SELECT *
FROM
	years_months
JOIN
	diff
    ON years_months.prefix = diff.prefix
    
LIMIT 1000
SQL

Checking query status...
