Untitled query #42584

SQL

USE enwiki_p;                -- wiki for source page; also the only wiki checked for redirects
SET @timestamp = '20180409'; -- search for any edit from this or later
SET @src_namespace = 4;      -- examine links from this namespace; 4 is Wikipedia:.
SET @src_page = 'VisualEditor/Newsletter';  -- examine links from this page in @src_namespace.  Replace spaces with _
SET @tgt_namespace = 3;      -- look for links to this namespace.  3 is user talk:, 2 is user, and others don't make sense here.

SELECT pl_title AS 'user name (as listed)',
       rd_title AS 'redirects to'
FROM pagelinks
JOIN page srcpg ON pl_from = srcpg.page_id
-- resolve redirects to other user talk: pages
LEFT JOIN page rdrpg ON rdrpg.page_namespace = @tgt_namespace AND rdrpg.page_title = pl_title
LEFT JOIN redirect ON rd_from = rdrpg.page_id AND rd_namespace = @tgt_namespace
WHERE srcpg.page_namespace = @src_namespace AND srcpg.page_title = @src_page
  AND pl_namespace = @tgt_namespace
-- The really, really ugly part.  Omit all but the first line to only query enwiki.
-- There's cleaner ways to do this, but not with the permissions available to Quarry users.
-- The list of databases is from "SELECT CONCAT(dbname, '_p') FROM meta_p.wiki WHERE is_closed = 0"
  AND NOT EXISTS (SELECT 1 FROM enwiki_p.revision_userindex JOIN enwiki_p.actor_revision ON actor_id = rev_actor WHERE actor_name = REPLACE(COALESCE(rd_title, pl_title), '_', ' ') AND rev_timestamp >= @timestamp)
;
SQL

Checking query status...
