Fork of Olympic articles by source by BilledMammal
This query is marked as a draft This query has been published by BilledMammal.

SQL

x
 
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 = 482
    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
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
All SQL code is licensed under CC0 License.

Query status: complete

Executed in 107.56 seconds as of Sun, 28 May 2023 02:27:08 UTC.