Fork of All Louisville area-related pages by StefenTower
This query is marked as a draft This query has been published by StefenTower.

SQL

AخA
 
# Louisville area-related articles with old issues
WITH RECURSIVE maincattree AS (
  SELECT cl1.cl_from
  FROM categorylinks cl1
  WHERE cl1.cl_to = CONCAT('Clean-up_categories_from_', '2007') -- Substitute the year of old issues you're interested in
  UNION
  SELECT cl1.cl_from
  FROM categorylinks cl1
  INNER JOIN page p ON cl1.cl_to = p.page_title AND p.page_namespace = 14
  INNER JOIN maincattree mct ON mct.cl_from = p.page_id
),
talkcat AS (
  SELECT cl2.cl_from
  FROM categorylinks cl2
  WHERE cl2.cl_to = 'WikiProject_Louisville_articles' -- substitute your project category of included articles, pages, etc.
)
SELECT ROW_NUMBER() OVER (ORDER BY p.page_title ASC) AS '#', p.page_title AS 'Article', SUBSTRING_INDEX(cl3.cl_to, "-", 1) AS 'Rating', 
  SUBSTRING_INDEX(cl4.cl_to, "-", 1) AS 'Importance', cl5.cl_to AS 'Cleanup Category'
FROM page p
INNER JOIN page tp ON p.page_title = tp.page_title AND tp.page_namespace = 1
LEFT JOIN categorylinks AS cl3 ON cl3.cl_from = tp.page_id
 AND cl3.cl_to REGEXP "^\\w+-Class_Louisville_articles$" -- substitute your project BASE category name for the rating
LEFT JOIN categorylinks AS cl4 ON cl4.cl_from = tp.page_id
 AND cl4.cl_to REGEXP "^\\w+-importance_Louisville_articles$" -- substitute your project BASE category name for project importance
LEFT JOIN categorylinks AS cl5 on cl5.cl_from = p.page_id AND cl5.cl_to LIKE '%2007' -- Substitute the year again
WHERE p.page_namespace = 0
AND p.page_id IN (SELECT mct.cl_from FROM maincattree mct)
AND tp.page_id IN (SELECT tc.cl_from FROM talkcat tc)
ORDER BY p.page_title ASC
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...