Fork of
High-traffic, unarchived talk pages
by Cryptic
This query is marked as a draft
This query has been published
by Cryptic.
SQL
x
SET @days = 90;
SET @min_edits = 30;
SET @min_timestamp = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -@days DAY), "%Y%m%d%H%i%s");
WITH freqs(f_page, f_title, f_editcount) AS
(
SELECT rev_page, page_title, COUNT(*)
FROM revision
JOIN page ON page_id = rev_page
WHERE page_namespace = 1
AND rev_timestamp >= @min_timestamp
GROUP BY page_title
HAVING COUNT(*) >= @min_edits
)
SELECT CONCAT('https://en.wikipedia.org/w/index.php?redirect=no&title=Talk:', f_title) AS link, f_editcount
FROM freqs
LEFT JOIN templatelinks ON tl_from = f_page
LEFT JOIN linktarget ON lt_id = tl_target_id AND lt_namespace = 2 AND lt_title IN ('ClueBot_III/ArchiveThis', 'MiszaBot/config')
WHERE tl_from IS NULL
ORDER BY f_editcount 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.