Untitled query #88975

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
    NOT user_name IN ('MF-Warburg', 'Jon Harald Søby', 'Minorax')
  HAVING 
    prefix <> ''
  ORDER BY 
    rev_id DESC),

  page_metrics AS (
    SELECT 
        prefix, 
        COUNT(DISTINCT page_id) AS unique_pages_count
    FROM 
        page
    WHERE 
        page_namespace IN (0, 1, 10, 11, 14, 15, 828, 829) AND
        page_is_redirect = 0 AND
        REGEXP_SUBSTR(page_title, 'W[a-z]/[a-z]+') <> ''
    GROUP BY 
        prefix
  ),

  primary_metrics AS (
    SELECT 
        prefix, 
        SUM(CASE WHEN byte_diff < 0 THEN byte_diff ELSE 0 END) AS bytes_removed_30D,
        SUM(CASE WHEN byte_diff >= 0 THEN byte_diff ELSE 0 END) AS bytes_added_30D,
        COUNT(DISTINCT actor_id) AS actor_count,
        COUNT(DISTINCT rev_id) AS edit_count
    FROM 
        base_table
    GROUP BY 
        prefix),

  last_3M AS (
    SELECT 
        *, 
        YEAR(rev_timestamp) AS year, 
        MONTH(rev_timestamp) AS month
    FROM 
        base_table
    WHERE 
        MONTH(rev_timestamp) >= MONTH(NOW()) - 3 AND 
        MONTH(rev_timestamp) < MONTH(NOW()) AND
        YEAR(rev_timestamp) = YEAR(NOW())),

  monthly_grouping_3M AS (
    SELECT 
        prefix, 
        month, 
        COUNT(DISTINCT rev_id) AS edits_by_month,
        COUNT(DISTINCT actor_id) AS editors_by_month
    FROM 
        last_3M
    GROUP BY 
        prefix, 
        month),

  avg_table_3M AS (
    SELECT 
        prefix, 
        AVG(edits_by_month) AS avg_edits_3M, 
        AVG(editors_by_month) AS avg_editors_3M
    FROM 
        monthly_grouping_3M
    GROUP BY
        prefix)

SELECT 
    pm.prefix, 
    edit_count, 
    actor_count,
    bytes_added_30D, 
    bytes_removed_30D, 
    unique_pages_count AS pages_count, 
    CAST(avg_edits_3M AS INT) AS avg_edits_3M, 
    CAST(avg_editors_3M AS INT) AS avg_editors_3M
FROM 
    primary_metrics pm
    JOIN avg_table_3M avg_3M ON avg_3M.prefix = pm.prefix
    JOIN page_metrics pg ON pg.prefix = pm.prefix
ORDER BY 
    avg_editors_3M DESC;
SQL

Checking query status...
