Louisville area-related articles with old issues per year (test)

SQL

# Louisville area-related articles with old issues
SET @CleanupYear = '2007';
SET @ProjectName = 'Louisville';
WITH RECURSIVE maincattree AS (
  SELECT cl1.cl_from
  FROM categorylinks cl1
  WHERE cl1.cl_to = CONCAT('Clean-up_categories_from_', @CleanupYear) -- 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 = CONCAT('WikiProject_', @ProjectName, '_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 CONCAT("^\\w+-Class_", @ProjectName, "_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 CONCAT("^\\w+-importance_", @ProjectName, "_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 CONCAT('%', @CleanupYear)
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;
SQL

Checking query status...
