Non-contributor user pages transcluding infobox person

Fork of Non-contributor user pages transcluding infobox person by Uhai

This query is marked as a draft This query has been published by EqJjgOa8rVvsRmZL.

User pages edited within the past year and not including subpages or users with more than 30 edits.

To do:
-Don't show pages with latest revision being by a bot
-Only count non-userspace edits towards the 30 edit threshold

SQL

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

SQL

Checking query status...