Gamsbart biographies by source

SQL

WITH 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 
JOIN page AS Talk ON Article.page_title = Talk.page_title AND Talk.page_namespace = 1
JOIN categorylinks AS ArticleCat ON ArticleCat.cl_from = Article.page_id
JOIN categorylinks AS TalkCat ON TalkCat.cl_from = Talk.page_id
WHERE rev_parent_id = 0
	AND Article.page_is_redirect = 0
  	AND rev_actor = 1132623
  	AND (TalkCat.cl_to LIKE '%iography%')
    #AND (ArticleCat.cl_to LIKE '%ricketers')
  	AND Article.page_len < 3000
),

reference AS (
  SELECT page_id, page_title, LEFT(el_index,20) AS ref
  FROM articles
  JOIN externallinks ON el_from = page_id
  WHERE LEFT(el_index,17) NOT IN ("http://com.google", 
                                  "https://org.jstor", 
                                  "https://com.googl", 
                                  "https://org.wikid", 
                                  "https://org.archi",
                                  "https://org.olymp",
                                  "http://org.olympi",
                                  "https://com.sport",
                                  "https://com.deepl",
                                  "https://goog.tran")
  AND LEFT(el_index,20) NOT IN ("https://com.espncric",
                                 "http://com.espncrici",
                                 "https://com.cricketa")
)

SELECT ref, COUNT(ref), page_title as example
FROM reference
GROUP BY ref
ORDER BY ref DESC
SQL

Checking query status...
