SQL
x
/* Featured articles and their FAC pages */
SELECT article.page_title, FAC.page_title, COUNT(*) AS CNT, COUNT(rev_page) AS Revs,
DATE_FORMAT(MIN(rev_timestamp), "%Y-%m-%d") AS First_FAC,
DATE_FORMAT(MAX(rev_timestamp), "%Y-%m-%d") AS Last_FAC,
SUBSTRING_INDEX(FAC.page_title, "/", -1) AS ArchiveNum,
-- SHA1(REPLACE(article.page_title, "_", " ")),
content_title
FROM categorylinks
JOIN page AS article ON article.page_namespace = 0 AND article.page_id = cl_from
JOIN page AS talk ON talk.page_namespace = 1 AND talk.page_title = article.page_title
JOIN pagelinks ON pl_from=talk.page_id AND pl_namespace=4 AND pl_title LIKE "%Featured_article_candidates/%"
JOIN page AS FAC ON FAC.page_namespace=pl_namespace AND FAC.page_title=pl_title
JOIN revision ON rev_page = FAC.page_id
JOIN (
SELECT page_title AS content_title, rev_sha1 AS content_sha1,
SUBSTR(LOWER(CONV(SUBSTR(rev_sha1, -12), 36, 16)), -6) as content_sha1_b64_end
FROM page
JOIN revision ON rev_id = page_latest
WHERE page_namespace=10 AND page_title LIKE "TFA_title/%"
-- GROUP BY 3 ORDER BY 4 DESC
) AS rev_sha1s ON content_sha1_b64_end = SUBSTR(LOWER(SHA1(REPLACE(article.page_title, "_", " "))), -6)
WHERE cl_to IN ("Featured_articles")
GROUP BY article.page_id
ORDER BY MAX(rev_timestamp) DESC
-- LIMIT 10;
/*/
SELECT page_title AS content_title, rev_sha1 AS content_sha1,
SUBSTR(CONV(SUBSTR(rev_sha1, -12), 36, 16), -6) as rev_sha1_36_64,
CONV(SUBSTR(rev_sha1,-12),36,16) as rev_sha1_36_64_s,
SUBSTR(SHA1("Der 100. Psalm"), -6)
FROM page
JOIN revision ON rev_id = page_latest
WHERE page_namespace=10 AND page_title LIKE "TFA_title/January_9,_2018";
/*-*/
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.