Article creations by editor and size

SQL

WITH all_creations AS (SELECT page_id, page_title, rev_actor
	FROM page
    JOIN revision ON page_id = rev_page
    WHERE page_namespace = 0
    	AND rev_parent_id = 0
		AND rev_timestamp >= '2018'
    	AND rev_timestamp < '2019'
        AND (revision.rev_len >= 150 #94% of redirects are smaller than this; 95% of articles are created longer.
        OR revision.rev_len < 16) #very few redirects are smaller than 16 bytes, but a not-insignificant amount of articles start out smaller
	LIMIT 10
                      ),

creations AS (SELECT page_id, page_title, rev_actor
	FROM all_creations
	LEFT JOIN categorylinks ON cl_from = page_id AND (
		cl_to IN ("All_article_disambiguation_pages", 
         "Temporary_maintenance_holdings", 
         "Redirects_to_Wiktionary",
         "Surnames", 
         "Given_names")
		OR cl_to LIKE 'List%')
	WHERE cl_to IS NULL
),

deltas AS (SELECT (CAST(child_rev.rev_len as SIGNED) - CAST((CASE WHEN parent_rev.rev_len IS NOT NULL THEN parent_rev.rev_len ELSE 0 END) as SIGNED)) as deltas, page_id, page_title, creations.rev_actor as creator_id, child_rev.rev_actor as editor_id
	FROM creations
    JOIN revision AS child_rev ON creations.page_id = child_rev.rev_page
    LEFT JOIN revision AS parent_rev ON child_rev.rev_parent_id = parent_rev.rev_id
)

SELECT creator.actor_name as Creator, editor.actor_name as Editor, page_title as Article, SUM(deltas) as Contributions
FROM deltas
JOIN actor AS creator on creator_id = creator.actor_id
JOIN actor AS editor on editor_id = editor.actor_id
GROUP BY page_title, editor.actor_id
SQL

Checking query status...
