Test - Quarry

SQL

USE ckbwiki_p;

SELECT p.page_title
FROM page p
LEFT JOIN revision r ON p.page_latest = r.rev_id
LEFT JOIN content c ON r.rev_id = c.rev_id
WHERE p.page_namespace = 0 -- articles only
AND p.page_is_redirect = 0 -- exclude redirects
AND r.rev_timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 30 DAY) AND NOW() -- last 30 days
AND p.page_latest = r.rev_id -- ensure latest revision
AND NOT EXISTS (SELECT 1 FROM revision r2 JOIN content c2 ON r2.rev_id = c2.rev_id WHERE r2.rev_page = p.page_id AND r2.rev_timestamp > r.rev_timestamp AND c2.old_text != c.old_text AND c2.old_text LIKE '%<ref%>') -- exclude articles with previous revisions that had references
AND c.old_text LIKE '%<ref%' = 0 -- exclude articles that currently contain <ref> tags
SQL

Checking query status...
