This query is marked as a draft This query has been published by Dispenser.

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.

Checking query status...