Carlossuarez46 Iranian villages external links

SQL

WITH RECURSIVE entries (page_title, page_id, page_namespace, inc, parent_title, parent_namespace, page_len) AS (
  (
    SELECT page_title, page_id, page_namespace, 1, page_title, page_namespace, page_len
    FROM page
    WHERE page_title IN ("Towns_and_villages_in_Iran_by_county")
      AND page_namespace = 14
  )
  UNION
  (
    SELECT child.page_title, child.page_id, child.page_namespace, inc + 1, parent.page_title, parent.page_namespace, child.page_len
    FROM page AS child
    JOIN categorylinks as childLinks ON child.page_id = childLinks.cl_from
    JOIN entries 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 = 14
      OR child.page_namespace = 0)
      AND parent.page_namespace = 14
      AND pp_propname IS NULL
      AND page_is_redirect = 0
      #AND inc < 4
  )
),

articles AS (
  SELECT DISTINCT(Article.page_title), Article.page_id, rev_timestamp, rev_len as "InitialSize", Article.page_len as "CurrentSize"
FROM revision_userindex
JOIN page AS Article ON Article.page_id = rev_page AND Article.page_namespace = 0 
WHERE rev_parent_id = 0
	AND Article.page_is_redirect = 0
  	AND rev_actor = 10795
  	AND Article.page_len < 2500
    AND Article.page_id IN (SELECT page_id FROM entries)
),

reference AS (
  SELECT LEFT(el_index,20) AS el_index
  FROM articles
  JOIN externallinks ON el_from = page_id
)

SELECT el_index, COUNT(*)
FROM reference
GROUP BY el_index
SQL

Checking query status...
