Untitled query #67203

SQL

WITH RECURSIVE get_name_dabs (page_title, page_id, page_namespace, inc, parent_title, parent_namespace) AS (
  (
    SELECT page_title, page_id, page_namespace, 1, page_title, page_namespace
    FROM page
    WHERE page_title IN ("Surnames", "Given_names")
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace, inc + 1, parent.page_title, parent.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
      #AND inc < 4
  )
),

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
  LEFT JOIN excluded_full on page_id = excluded
  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
    AND excluded IS NULL
  GROUP BY actor_name
  HAVING creation_count > 4
)

#SELECT *
#FROM get_name_dabs

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

Checking query status...
