Citations with redundant publisher links (new schema), recently edited

Fork of Citations with redundant publisher links (new schema) by Nemo bis

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

Pages edited by oabot but not citation bot in the past 3 months. May need further cleanup.

SQL

USE enwiki_p;
SELECT p.page_title, max(r2.rev_timestamp) AS oabot_ts, max(r3.rev_timestamp) AS citbot_ts
FROM page p
JOIN revision r2
ON r2.rev_page = p.page_id
AND p.page_namespace = 0
AND r2.rev_actor = 29612637
AND r2.rev_timestamp > DATE(NOW() - INTERVAL 3 MONTH)
JOIN revision r3
ON r3.rev_page = p.page_id
AND r3.rev_actor = 307
AND r3.rev_timestamp > DATE(NOW() - INTERVAL 3 MONTH)
AND p.page_id IN (SELECT DISTINCT(el_from)
                FROM externallinks
                WHERE el_to_domain_index LIKE 'http://com.tandfonline.www.'
                OR el_to_domain_index LIKE 'https://com.tandfonline.www.'
                OR el_to_domain_index LIKE 'http://com.sciencedirect.www.'
                OR el_to_domain_index LIKE 'https://com.sciencedirect.www.'
                OR el_to_domain_index LIKE 'http://com.elsevier.linkinghub.'
                OR el_to_domain_index LIKE 'https://com.elsevier.linkinghub.'
                OR el_to_domain_index LIKE 'http://com.elsevierhealth.journals.www.'
                OR el_to_domain_index LIKE 'https://com.elsevierhealth.journals.www.'
                OR el_to_domain_index LIKE 'http://com.springer.link.'
                OR ( el_to_domain_index LIKE 'https://com.springer.link.'
                    AND NOT el_to_path LIKE '/journal/%' )
                OR el_to_domain_index LIKE 'http://com/doi.sagepub.journals.'
                OR el_to_domain_index LIKE 'https://com/doi.sagepub.journals.'
                OR el_to_domain_index LIKE 'http://org/doi.acs.pubs.'
                OR el_to_domain_index LIKE 'https://org/doi.acs.pubs.'
                OR el_to_domain_index LIKE 'http://org.ieee.ieeexplore.'
                OR el_to_domain_index LIKE 'https://org.ieee.ieeexplore.'
                OR ( el_to_domain_index LIKE 'http://com.nature.www.' 
                    AND el_to_path NOT LIKE '/news/%' )
                OR ( el_to_domain_index LIKE 'https://com.nature.www.'
                    AND el_to_path NOT LIKE '/news/%' )
                OR el_to_domain_index LIKE 'http://org.sciencemag.www.'
                OR el_to_domain_index LIKE 'https://org.sciencemag.www.'
                OR el_to_domain_index LIKE 'http://org.sciencemag.science.'
                OR el_to_domain_index LIKE 'https://org.sciencemag.science.'
                OR el_to_domain_index LIKE 'http://com.oup.academic.'
                OR el_to_domain_index LIKE 'https://com.oup.academic.'
                OR el_to_domain_index LIKE 'http://org.cambridge.journals.'
                OR el_to_domain_index LIKE 'https://org.cambridge.journals.'
                OR el_to_domain_index LIKE 'https://org.cambridge.www.'
                OR el_to_domain_index LIKE 'http://org.iop.iopscience.'
                OR el_to_domain_index LIKE 'https://org.iop.iopscience.'
                OR el_to_domain_index LIKE 'http://org.iop.stacks.'
                OR el_to_domain_index LIKE 'http://com.cell.www.'
                OR el_to_domain_index LIKE 'https://com.cell.www.'
                OR el_to_domain_index LIKE 'http://com.thelancet.www.'
                OR el_to_domain_index LIKE 'https://com.thelancet.www.'
                OR el_to_domain_index LIKE 'http://com.bmj.www.'
                OR el_to_domain_index LIKE 'https://com.bmj.www.'
                OR el_to_domain_index LIKE 'http://com.ingentaconnect.www.'
                OR el_to_domain_index LIKE 'https://com.ingentaconnect.www.'
                OR ( el_to_domain_index LIKE 'http://org.acm.dl.'
                    AND el_to_path NOT LIKE '/author_page.cfm%' )
                OR ( el_to_domain_index LIKE 'https://org.acm.dl.'
                    AND el_to_path NOT LIKE '/author_page.cfm%' )
                OR el_to_domain_index LIKE 'http://org.acm.doi.'
                OR el_to_domain_index LIKE 'http://com.wkhealth.meta.'
                OR el_to_domain_index LIKE 'https://com.wkhealth.meta.'
                OR el_to_domain_index LIKE 'http://com.lww.journals.'
                OR el_to_domain_index LIKE 'https://com.lww.journals.'
                OR el_to_domain_index LIKE 'http://org.nejm.www.'
                OR el_to_domain_index LIKE 'https://org.nejm.www.'
                OR el_to_domain_index LIKE 'http://org.apa.psycnet.'
                OR el_to_domain_index LIKE 'https://org.apa.psycnet.'
                OR el_to_domain_index LIKE 'http://org.ama-assn.jama.'
                OR el_to_domain_index LIKE 'http://com.jamanetwork.jama.'
                OR el_to_domain_index LIKE 'https://org.ama-assn.jama.'
                OR el_to_domain_index LIKE 'https://com.jamanetwork.jama.'
                OR el_to_domain_index LIKE 'http://com.informaworld.www.'
                OR el_to_domain_index LIKE 'http://org.royalsocietypublishing.%'
                OR ( ( el_to_domain_index LIKE 'http://com.wiley.onlinelibrary%'
                      OR el_to_domain_index LIKE 'https://com.wiley.onlinelibrary%' )
                   AND NOT el_to_path LIKE '/journal/%' )
                OR el_to_domain_index LIKE 'http://com.wiley.doi%'
                OR el_to_domain_index LIKE 'https://com.els-cdn.%'
                OR el_to_domain_index LIKE 'http://com.serialssolutions.%'
                OR el_to_domain_index LIKE 'https://org.oclc.idm.%'
               )
GROUP BY p.page_title
HAVING oabot_ts > citbot_ts;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...