Untitled query #89010

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
  FROM 
    revision rev
  JOIN 
    page 
    ON rev.rev_page = page.page_id
  JOIN 
    actor
    ON rev.rev_actor = actor.actor_id
  LEFT OUTER JOIN 
    recentchanges rc 
    ON rev.rev_id = rc.rc_this_oldid
  JOIN
    user
    ON actor.actor_name = user.user_name
  WHERE 
    page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829) AND
    page_is_redirect = 0 AND
    -- explicitly remove admins who often make edits across multiple incubating projects
    NOT user_name IN ('MF-Warburg', 'Jon Harald Søby', 'Minorax')
  HAVING 
    prefix <> ''
  ORDER BY 
    rev_id DESC
),

primary_metrics AS (
  SELECT 
    actor_id,
    COUNT(DISTINCT rev_id) AS edit_count
  FROM 
    base_table
  GROUP BY 
    actor_id
  HAVING 
    COUNT(DISTINCT rev_id) > 10
)

SELECT 
    actor_id,
    edit_count
FROM 
    primary_metrics;
SQL

Checking query status...
