This query is marked as a draft This query has been published by CMyrick-WMF.

SQL

x
 
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),
  actor_table AS (
  SELECT page_namespace, COUNT(DISTINCT actor_id) AS actor_count
  FROM base_table
  GROUP BY page_namespace),
  page_table AS (
  SELECT page_namespace, COUNT(DISTINCT page_id) AS pages_count
  FROM base_table
  GROUP BY page_namespace),
  
  before_grad AS (
    SELECT *, YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month
    FROM base_table
    WHERE MONTH(rev_timestamp) < 8
    AND YEAR(rev_timestamp) < 2019),
    
  after_grad AS (
    SELECT *, YEAR(rev_timestamp) AS year, MONTH(rev_timestamp) AS month
    FROM base_table
    WHERE MONTH(rev_timestamp) >= 8
    AND YEAR(rev_timestamp) >= 2019),
  
  monthly_grouping_before_grad AS (
    SELECT page_namespace, month, 
        COUNT(DISTINCT rev_id) AS edits_by_month_bg,
        COUNT(DISTINCT actor_id) AS editors_by_month_bg
    FROM before_grad
    GROUP BY page_namespace, month, year),
    
  monthly_grouping_after_grad AS (
    SELECT page_namespace, month, 
        COUNT(DISTINCT rev_id) AS edits_by_month_ag,
        COUNT(DISTINCT actor_id) AS editors_by_month_ag
    FROM after_grad
    GROUP BY page_namespace, month, year),
    
  avg_table_before_grad AS (
    SELECT page_namespace, 
        AVG(edits_by_month_bg) AS avg_monthly_edits_before_grad
        AVG(editors_by_month_bg) AS avg_monthly_editors_before_grad
    FROM monthly_grouping
    GROUP BY page_namespace),
    
  avg_table_after_grad AS (
    SELECT page_namespace, 
        AVG(edits_by_month_ag) AS avg_monthly_edits_after_grad
        AVG(editors_by_month_ag) AS avg_monthly_editors_after_grad
    FROM monthly_grouping
    GROUP BY page_namespace)
SELECT e.page_namespace,  
        CAST(avg_monthly_editors_before_grad AS INT) AS avg_monthly_edits_before, 
        CAST(avg_monthly_editors_after_grade AS INT) AS avg_monthly_editors_after
FROM edits_table e
JOIN actor_table a ON a.page_namespace = e.page_namespace
JOIN avg_table_before_grad ON avg_table_before_grad.page_namespace = e.page_namespace
JOIN avg_table_after_grad ON avg_table_after_grad.page_namespace = e.page_namespace    
ORDER BY e.edit_count DESC
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Checking query status...