Incubator wikis, first rev to last rev, (based on rev table), September 2022

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 LIMIT 50
      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,
      	  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),
           
    diff AS (
      SELECT
        prefix,
      	year_max_rev - year_min_rev AS year_diff,
        month_max_rev - month_min_rev AS month_diff
      FROM
        years_months)
           

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

Checking query status...
