SET @basecat = 'Companies by country';

SET max_recursive_iterations = 3; -- enough for 91743 articles

​

SET @basecat = REPLACE(@basecat, ' ', '_');

WITH RECURSIVE deepcat(subcat) AS

(

  SELECT @basecat

​

  UNION DISTINCT

​

  SELECT page_title

  FROM categorylinks

  JOIN page ON page_id = cl_from AND page_namespace = 14

  JOIN deepcat ON cl_to = subcat

  WHERE page_title NOT LIKE 'People\_by\_company%'

),

company_articles(ca_title, ca_page_id, ca_timestamp) AS

(

  SELECT page_title, page_id, MIN(rev_timestamp)

  FROM page

  JOIN categorylinks ON cl_from = page_id

  JOIN deepcat ON cl_to = subcat

  JOIN revision ON rev_page = page_id

  WHERE page_namespace = 0

    AND page_is_redirect = 0

  GROUP BY page_id

)

SELECT ca_title,

       user_name,

       user_editcount,

       CAST(user_registration AS DATETIME) AS 'user registration',

       CAST(ca_timestamp AS DATETIME) AS 'page creation'

FROM company_articles

JOIN revision ON rev_page = ca_page_id AND rev_timestamp = ca_timestamp

JOIN actor_revision ON actor_id = rev_actor

JOIN user ON user_id = actor_user

WHERE user_registration >= DATE_FORMAT(DATE_ADD(ca_timestamp, INTERVAL -1 MONTH), '%Y%m%d%H%i%s')

   OR user_editcount <= 500;