dewiki: Find not archived URLs where the same URL on same page is archived (editmode).

SQL

USE dewiki_p;
SELECT distinct farchivepage, archivefound, el_to
FROM externallinks as eur 
   INNER JOIN page as pr on eur.el_from=pr.page_id
   INNER JOIN (SELECT 
               pf.page_id
             , substring(euf.el_to,29+15) as f
             , concat('[', max(euf.el_to),' ', substring(euf.el_to,29,14), ']') as archivefound
             , CONCAT('[',GROUP_CONCAT(
                  CONCAT('https://de.wikipedia.org/w/index.php?action=edit&curid=',pf.page_id, ' ', pf.page_title) SEPARATOR '], ['),']') as farchivepage
             , max(euf.el_id) as m_id
        FROM externallinks as euf INNER JOIN page as pf on euf.el_from=pf.page_id
        WHERE el_index LIKE "https://org.archive.web./web/______________/%" 
        and pf.page_namespace=0 
        GROUP BY substring(euf.el_to,29+15),pf.page_id      ) as filter on filter.f=el_to and filter.page_id=pr.page_id

WHERE pr.page_namespace=0 
GROUP BY el_to
ORDER BY farchivepage DESC              
LIMIT 15000

SQL

Checking query status...