SQL
AخA
WITH infobox_template AS (
SELECT tl.tl_from FROM
enwiki_p.templatelinks tl
WHERE tl.tl_target_id = 1182
)
SELECT
CONCAT('https://en.wikipedia.org/wiki/User:', p.page_title) AS 'User page',
u.user_editcount AS 'User edit count',
TIMESTAMP(r.rev_timestamp) AS 'Page last edited',
p.page_len AS 'Page length',
CASE WHEN p.page_is_new THEN 'Yes' ELSE 'No' END AS 'Is new?'
FROM page p
INNER JOIN user u ON REPLACE(p.page_title, '_', ' ') = u.user_name
INNER JOIN revision_userindex r ON p.page_latest = r.rev_id
WHERE p.page_namespace = 2
AND p.page_id IN (SELECT tl_from FROM infobox_template)
-- AND p.page_title NOT LIKE '%/%'
AND u.user_editcount <= 30
AND r.rev_timestamp > DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY r.rev_timestamp DESC
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.