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.