Fork of
Company articles created by non-EC users
by Cryptic
This query is marked as a draft
This query has been published
by Teratix.
SQL
x
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 0 AS 'page_namespace',
ca_title AS 'page_title'
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_groups ON actor_user = ug_user
WHERE ug_group = 'extendedconfirmed';
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.