Article creations by editor and size

SQL

WITH excluded_pages AS (SELECT cl_from
	FROM categorylinks
	WHERE cl_to IN 
		("All_article_disambiguation_pages", 
         "Temporary_maintenance_holdings", 
         "Redirects_to_Wiktionary",
         "Surnames", 
         "Given_names")
),

all_revisions AS (SELECT *
	FROM revision
	JOIN page on page_id = rev_page
	JOIN actor_revision ON actor_id = rev_actor
	LEFT JOIN excluded_pages ON page_id = cl_from
	WHERE cl_from IS NULL
    	AND page_namespace = 0
		AND rev_timestamp >= '2018'
    	AND rev_timestamp < '2019'
),

article_creations AS (SELECT *
	FROM all_revisions
    WHERE rev_parent_id = 0
),

editor_contributions AS (SELECT actor_name, page_title, sum(rev_len) AS contributions, rev_parent_id
	FROM all_revisions
    WHERE (rev_len >= 150 #94% of redirects are smaller than this; 95% of articles are created longer.
       OR rev_len < 16) #very few redirects are smaller than 16 bytes, but a not-insignificant amount of articles start out smaller
    GROUP BY rev_page, rev_actor
)

SELECT *
FROM editor_contributions
SQL

Checking query status...
