Untitled query #67203

SQL

WITH RECURSIVE get_name_dabs (page_title, page_id, page_namespace) AS (
  (
    SELECT page_title, page_id, page_namespace
    FROM page
    WHERE page_title IN ("Surname", "Given names")
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace
    FROM page AS child
    JOIN categorylinks as childLinks ON child.page_id = childLinks.cl_from
    JOIN get_name_dabs AS parent ON childLinks.cl_to = parent.page_title
    LEFT JOIN page_props ON childLinks.cl_from = pp_page AND pp_propname = "hiddencat"
    WHERE (child.page_namespace = 0
      OR child.page_namespace = 14)
      AND parent.page_namespace = 14
      AND pp_propname IS NULL
  )
),

name_dabs AS (
  SELECT DISTINCT(page_id), page_title
  FROM get_name_dabs
  WHERE page_namespace = 0
),

disambiguation_pages AS (SELECT cl_from
  FROM categorylinks
  WHERE cl_to = "All_article_disambiguation_pages"
),

excluded_full AS (
  (
    SELECT cl_from as excluded
	FROM disambiguation_pages
  )
  UNION
  (
    SELECT page_id as excluded
    FROM name_dabs
  )
),

articles AS (
  SELECT actor_name, count(*) as creation_count, sum(page_len) as creation_size
  FROM revision
  JOIN actor_revision ON actor_id = rev_actor
  JOIN page ON page_id = rev_page
  WHERE rev_parent_id = 0
  AND page_namespace = 0
  AND page_is_redirect = 0
  AND rev_timestamp >= '2020'
  AND (rev_len > 149 #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 actor_name
),

SELECT
	CASE creation_count
        WHEN < 25 THEN "Under 25 articles"
        WHEN < 100 THEN "Under 100 articles"
        WHEN < 200 THEN "Under 200 articles"
        WHEN < 300 THEN "Under 300 articles"
        WHEN < 400 THEN "Under 400 articles"
        WHEN < 500 THEN "Under 500 articles"
        WHEN < 600 THEN "Under 600 articles"
        WHEN < 700 THEN "Under 700 articles"
        WHEN < 800 THEN "Under 800 articles"
        WHEN < 900 THEN "Under 900 articles"
        WHEN < 1000 THEN "Under 1000 articles"
        ELSE "Over 1000 articles"
    END as creation_range,
    SUM(creation_size) / SUM(creation_count)
    FROM articles
    WHERE creation_count > 4
    GROUP BY creation_range
SQL

Checking query status...
